WSO2 Stream processor Integrating with mysql database is not working

Question

I am trying to connect the mysql server through siddhi application and I followed the below documentation link for the setup. https://docs.wso2.com/display/SP430/Integrating+Datastores

But when I run the siddhi application I am getting the below error,

[2018-11-05_05-22-38_825] INFO {org.wso2.carbon.siddhi.editor.core.internal.WorkspaceDeployer} - Siddhi App ShipmentHistoryApp successfully deployed. 
ShipmentHistoryApp.siddhi - Started Successfully!
[2018-11-05_05-22-46_761] ERROR {org.wso2.siddhi.core.table.Table} - Error on 'ShipmentHistoryApp'. Exception during pool initialization: Communications link failure__The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. . Error while connecting to Table 'ShipmentDetails'. (Encoded)
com.zaxxer.hikari.pool.PoolInitializationException: Exception during pool initialization: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.zaxxer.hikari.pool.HikariPool.initializeConnections(HikariPool.java:581)
    at com.zaxxer.hikari.pool.HikariPool.(HikariPool.java:152)
    at com.zaxxer.hikari.HikariDataSource.(HikariDataSource.java:73)
    at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.initializeDatasource(RDBMSEventTable.java:1002)
    at org.wso2.extension.siddhi.store.rdbms.RDBMSEventTable.connect(RDBMSEventTable.java:795)
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:174)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:64)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:835)
    at com.mysql.cj.jdbc.ConnectionImpl.(ConnectionImpl.java:455)
    ... 21 more
Caused by: com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
Caused by: java.net.SocketException: Broken pipe (Write failed)
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
    at sun.security.ssl.OutputRecord.writeBuffer(OutputRecord.java:431)
    at sun.security.ssl.OutputRecord.write(OutputRecord.java:417)
    at sun.security.ssl.SSLSocketImpl.writeRecordInternal(SSLSocketImpl.java:879)
    at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:850)
    at sun.security.ssl.SSLSocketImpl.writeRecord(SSLSocketImpl.java:720)
[2018-11-05_05-22-46_762] ERROR {org.wso2.siddhi.core.SiddhiAppRuntime} - Error starting Siddhi App 'ShipmentHistoryApp', triggering shutdown process. Exception during pool initialization: Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Below is the siddhi app i created in WSO2 stream processor studio,

@app:name('ShipmentHistoryApp')

@source(type = 'http', receiver.url='http://localhost:8280/ShipmentHistoryApp/RawMaterialStream', @Map(type = 'json'))
define stream RawMaterialStream(name string, supplier string, amount double);

@PrimaryKey('name')
@index('supplier')
@store(type='rdbms', jdbc.url="jdbc:mysql://localhost:3306/SweetFactoryDB", username="root", password="root@123" , jdbc.driver.name="com.mysql.cj.jdbc.Driver")
define table ShipmentDetails(name string, supplier string, amount double);

from RawMaterialStream
select name, supplier, amount
insert into ShipmentDetails;

Below is the mysql configuration I have defined in the editor deployment.yaml

  - name: SweetFactoryDB
    description: Datasource used for Sweet Factory Supply Records
    jndiConfig:
      name: jdbc/SweetFactoryDB
      useJndiReference: true
    definition:
      type: RDBMS
      configuration:
        jdbcUrl: 'jdbc:mysql://siddhi:3306/SweetFactoryDB'
        username: root
        password: root@123
        driverClassName: com.mysql.cj.jdbc.Driver
        maxPoolSize: 50
        idleTimeout: 60000
        connectionTestQuery: SELECT 1
        validationTimeout: 30000
        isAutoCommit: false

I tried converting the mysql driver to OSGI(Using jartobundle.sh) before I put into /lib directory:

misra_debasish91@siddhi:/usr/lib/wso2/wso2sp/4.3.0$ sh /usr/lib/wso2/wso2sp/4.3.0/bin/jartobundle.sh mysql-connector-java-8.0.13.jar lib/
JAVA_HOME environment variable is set to /usr/lib/jvm/java-8-oracle
CARBON_HOME environment variable is set to /usr/lib/wso2/wso2sp/4.3.0
Nov 05, 2018 7:00:41 AM org.wso2.carbon.tools.converter.utils.BundleGeneratorUtils convertFromJarToBundle
WARNING: Path /usr/lib/wso2/wso2sp/4.3.0/mysql-connector-java-8.0.13.jar refers to an OSGi bundle
Nov 05, 2018 7:00:41 AM org.wso2.carbon.tools.converter.utils.BundleGeneratorUtils convertFromJarToBundle
INFO: Created the OSGi bundle mysql_connector_java_8.0.13_1.0.0.jar for JAR file /usr/lib/wso2/wso2sp/4.3.0/mysql-connector-java-8.0.13.jar

Please help me to resolve the error and correct me If I am doing anything wrong in the setup.

Thanks!!

Answer 1

I had the same problem. Add ?useSSL=false parameter for datasource worked for me. Mysql in Aurora RDS

Answer 2

There are few things that needs to be corrected in above configuration. In the shared Siddhi app within @store annotation you are providing all DB parameters to create the DB connection. This is one mode of connecting to stores. Anther method is to define a datasource in deployment.yaml file and use it. Since you have already defined your store in-line let's first remove the datasource definition that you have added in deployment.yaml. My guess is this connection issue in the logs is occurring due to URL jdbc:mysql://siddhi:3306/SweetFactoryDB as hostname siddhi is not resolvable.

Another point to note is that recent mqsql drivers are already OSGified. So please check the particular driver jar is already an OSGi bundle. In that case you don't need to convert.

With above tips and shared documentation you should be able to achieve your requirement.