Communications link failure with MySQL db

Overview

An error message may be appended to JBoss' log file while using Talend MDM (Master Data Management) with MySQL DB. This article describes this error and provides solutions to eliminate it.

 

Symptoms/Description

When using Talend MDM with MySQL DB, the following error may occur several times in the jboss server log file jboss-4.2.2.GA\server\default\log\server.log:

** BEGIN NESTED EXCEPTION ** 
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException
MESSAGE: Communications link failure
Last packet sent to the server was 1 ms ago.
STACKTRACE:
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
Last packet sent to the server was 1 ms ago.
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
	at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3134)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1818)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)

 

Resolution

To resolve the problem, follow these steps:

  1. Increase the wait_timeout in mysql db setting file my.ini (Windows) or my.cnf (Linux). e.g. On Linux, modify the value in /etc/my.cnf file as below:

    wait_timeout=2814400

    Then, re-start mysql db.

  2. Add the hibernate properties shown below in 'MySQL-Default' master & staging in Talend MDM db setting file jboss-4.2.2.GA\bin\datasources.xml:

    <datasource name="MySQL-Default">
        <master>
            <type>RDBMS</type>
            <rdbms-configuration>
            ...
            <properties>
            <property name="connection.autoReconnect">true</property>
            <property name="connection.autoReconnectForPools">true</property>
           	<property name="connection.is-connection-validation-required">true</property>
            <property name="hibernate.c3p0.acquire_increment">1</property>
            <property name="hibernate.c3p0.idle_test_period">0</property>
            <property name="hibernate.c3p0.timeout">0</property>
            <property name="hibernate.c3p0.validate">true</property>
            </properties>
            ... 
Version history
Revision #:
12 of 12
Last update:
‎04-14-2019 02:26 PM
Updated by:
 
Labels (2)
Comments
Five Stars

I use talend for a school project and I had the same problem.

I did not find wait_timeout in my.ini.

3.PNG4.PNGHere is error

 

Community Manager

Hi salmen_ft

Just add a new line in the configuration file if it does not exist, save the file and restart your Mysql Server.

 

btw, if you want to query the value of time_out parameter, execute:

 

mysql﹥ show global variables like 'wait_timeout'; 

+---------------+---------+ 

| Variable_name | Value | 

+---------------+---------+ 

| wait_timeout | 28800 | 

+---------------+---------+ 

1 row in set (0.00 sec) 

 

Regards

Shong

 

 

Four Stars

I have this same issue when inserting from Snowflake (warehouse) to MySql (application database). In my case, at the present time, the timeout for MySql cannot be changed, so I needed a work-around.

 

Below is the previous job outline and work-around Previous job did the following:

1. Open MySql

2. Get MAX Load_Date from table and save value as context.loadDate

3. Use context.loadDate and select from Snowflake updates/inserts

4. Pass results as Input to tMap

5. Pass output to tMySqlOutput

6. Close MySql Communications link failure with MySQL db occurred at step 5.

 

Work-around job does the following:

1. Open MySql-connection-1

2. Get MAX Load_Date from table and save value as context.loadDate

3. Use context.loadDate and select from Snowflake updates/inserts

4. Close MySql-connection-1

5. Open MySql-connection-2

6. Use context.loadDate and select from Snowflake updates/inserts. This time select is from Snowflake cache and immediately returns.

7. Pass results as Input to tMap

8. Pass output to tMySqlOutput

9. Close MySql-connection-2

Community Manager

Hi dflansburg

Thanks for sharing your work-around to community. In summary, you created two Mysql connections, one for reading data and another for inserting data to avoid the timeout error.

Regards

Shong