CODEWITHSUNDEEP
javamysqljdbctalend
Abdul Manaf
Abdul Manaf

Reputation: 4888

Getting Error com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

I have some data migration scripts ( shell scripts generated from talend data migration tool) which connects to MySQL and perform some operations.

One of the script is performs heavy calculations and make lockups from when i execute it on my local machine it completes in around 2.5 hours,and the connection made to MySQL stays in sleep mode

From MySQL Processlist 

mysql> show processlist;
+-------+------+---------------------+-------------------+---------+------+-------+------------------+
| Id    | User | Host                | db                | Command | Time | State | Info             |
+-------+------+---------------------+-------------------+---------+------+-------+------------------+
| 10631 | root | localhost           | psdata_psdatabase | Sleep   |   18 |       | NULL             |
| 11195 | root | localhost           | psdata_psdatabase | Sleep   | 5497 |       | NULL             |
| 11261 | root | localhost           | psdata_psdatabase | Query   |    0 | NULL  | show processlist |
| 11492 | root | 192.168.9.213:56507 | psdata_psdatabase | Sleep   | 5509 |       | NULL             |
| 11493 | root | 192.168.9.213:56508 | psdata_psdatabase | Sleep   | 5508 |       | NULL             |
+-------+------+---------------------+-------------------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

The Threads from 192.168.9.213 are of that script.

But when i execute same script on production/staging, I am getting error after around 1 hour, And I also don't see any activity in MySQL Processlist in that one hour as there were connections in sleep mode on my local machine 

"com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure"

Stack Trace 

[root@host5 /home/talend/PharmaSecure_psData_localhost/execute_analysis_jobs]# sh execute_analysis_jobs_run.sh 
Analysis Started
Started psVerify_interaction_analysis
Ended psVerify_interaction_analysis
Started psVerify_interaction_analysis_p2
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 3,350,869 milliseconds ago.  The last packet sent successfully to the server was 31 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3851)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2471)
    at com.mysql.jdbc.MysqlIO.disableMultiQueries(MysqlIO.java:3771)
    at com.mysql.jdbc.PreparedStatement.executePreparedBatchAsMultiStatement(PreparedStatement.java:1675)
    at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1463)
    at pharmasecure_ps.psverify_interaction_analysis_p2_0_1.psVerify_interaction_analysis_p2.tMysqlInput_1Process(psVerify_interaction_analysis_p2.java:3576)
    at pharmasecure_ps.psverify_interaction_analysis_p2_0_1.psVerify_interaction_analysis_p2.tMysqlInput_6Process(psVerify_interaction_analysis_p2.java:1196)
    at pharmasecure_ps.psverify_interaction_analysis_p2_0_1.psVerify_interaction_analysis_p2.tJava_1Process(psVerify_interaction_analysis_p2.java:687)
    at pharmasecure_ps.psverify_interaction_analysis_p2_0_1.psVerify_interaction_analysis_p2.runJobInTOS(psVerify_interaction_analysis_p2.java:7365)
    at pharmasecure_ps.psverify_interaction_analysis_p2_0_1.psVerify_interaction_analysis_p2.runJob(psVerify_interaction_analysis_p2.java:7207)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tRunJob_1Process(psVerify_interaction_analysis.java:4911)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tJava_2Process(psVerify_interaction_analysis.java:4806)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_8Process(psVerify_interaction_analysis.java:4719)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_6Process(psVerify_interaction_analysis.java:2821)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_5Process(psVerify_interaction_analysis.java:1541)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_9Process(psVerify_interaction_analysis.java:6433)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tJava_1Process(psVerify_interaction_analysis.java:5924)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.runJobInTOS(psVerify_interaction_analysis.java:6652)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.runJob(psVerify_interaction_analysis.java:6494)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_4Process(execute_analysis_jobs.java:1174)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_1Process(execute_analysis_jobs.java:838)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tJava_1Process(execute_analysis_jobs.java:682)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.runJobInTOS(execute_analysis_jobs.java:2749)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.main(execute_analysis_jobs.java:2596)
Caused by: java.net.SocketException: Broken pipe
    at java.net.SocketOutputStream.socketWrite0(Native Method)
    at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
    at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
    at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
    at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
    at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3832)
    ... 23 more
Exception in component tRunJob_1
java.lang.RuntimeException: Child job running failed
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tRunJob_1Process(psVerify_interaction_analysis.java:4932)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tJava_2Process(psVerify_interaction_analysis.java:4806)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_8Process(psVerify_interaction_analysis.java:4719)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_6Process(psVerify_interaction_analysis.java:2821)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_5Process(psVerify_interaction_analysis.java:1541)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tMysqlInput_9Process(psVerify_interaction_analysis.java:6433)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.tJava_1Process(psVerify_interaction_analysis.java:5924)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.runJobInTOS(psVerify_interaction_analysis.java:6652)
    at pharmasecure_ps.psverify_interaction_analysis_0_1.psVerify_interaction_analysis.runJob(psVerify_interaction_analysis.java:6494)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_4Process(execute_analysis_jobs.java:1174)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_1Process(execute_analysis_jobs.java:838)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tJava_1Process(execute_analysis_jobs.java:682)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.runJobInTOS(execute_analysis_jobs.java:2749)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.main(execute_analysis_jobs.java:2596)
Exception in component tRunJob_4
java.lang.RuntimeException: Child job running failed
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_4Process(execute_analysis_jobs.java:1195)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_1Process(execute_analysis_jobs.java:838)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tJava_1Process(execute_analysis_jobs.java:682)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.runJobInTOS(execute_analysis_jobs.java:2749)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.main(execute_analysis_jobs.java:2596)
Exception in component tSendMail_2
javax.mail.AuthenticationFailedException: failed to connect
    at javax.mail.Service.connect(Service.java:322)
    at javax.mail.Service.connect(Service.java:172)
    at javax.mail.Service.connect(Service.java:121)
    at javax.mail.Transport.send0(Transport.java:190)
    at javax.mail.Transport.send(Transport.java:120)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tSendMail_2Process(execute_analysis_jobs.java:1433)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_4_onSubJobError(execute_analysis_jobs.java:508)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.tRunJob_4_error(execute_analysis_jobs.java:384)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs$TalendException.printStackTrace(execute_analysis_jobs.java:330)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs$TalendException.printStackTrace(execute_analysis_jobs.java:318)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs$TalendException.printStackTrace(execute_analysis_jobs.java:318)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.runJobInTOS(execute_analysis_jobs.java:2755)
    at pharmasecure_ps.execute_analysis_jobs_0_1.execute_analysis_jobs.main(execute_analysis_jobs.java:2596)

Upvotes: 1

Views: 8866

Answers (1)

Mat777
Mat777

Reputation: 338

Check the value of the wait_timeout variable on your mysql servers. The value of the variable could be too low on your production server.

To show the value type this command :

SHOW VARIABLES  LIKE 'wait_timeout'

Set the same value in your local server as the production to see if you can reproduce the problem. If the problem can be reproduced, you may have to increase the value of the wait_timeout on your production server.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout

Upvotes: 1

Related Questions