Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Communications link failure with MySQL

  1. #1
    Join Date
    Jun 2013
    Posts
    24

    Question Communications link failure with MySQL

    I am getting this error near the end of a long transformation. Can someone give some ideas on what the problem might be? I have searched the forum and found similar "Communications link failure" issues but the suggested fix doesn't make a difference in this case. I increased net_write_timeout = 600 from 100.

    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unexpected error
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error in step, asking everyone to stop because of:
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error looking up row in database
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Communications link failure
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : The last packet successfully received from the server was 1,290,343 milliseconds ago. The last packet sent successfully to the server was 1,290,448 milliseconds ago.
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:318)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.lang.Thread.run(Thread.java:695)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error looking up row in database
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Communications link failure
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : The last packet successfully received from the server was 1,290,343 milliseconds ago. The last packet sent successfully to the server was 1,290,448 milliseconds ago.
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.getLookup(Database.java:3082)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.getLookup(Database.java:3055)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.lookupValues(InsertUpdate.java:91)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.trans.steps.insertupdate.InsertUpdate.processRow(InsertUpdate.java:301)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 2 more
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) :
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : The last packet successfully received from the server was 1,290,343 milliseconds ago. The last packet sent successfully to the server was 1,290,448 milliseconds ago.
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3938)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2551)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2322)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at org.pentaho.di.core.database.Database.getLookup(Database.java:3063)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 5 more
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Caused by: java.net.SocketException: Broken pipe
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.net.SocketOutputStream.socketWrite0(Native Method)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.net.SocketOutputStream.write(SocketOutputStream.java:136)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3919)
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : ... 11 more
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unable to commit connection(s) rg.pentaho.di.core.exception.KettleDatabaseException:
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unable to get database metadata from this database connection
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : No operations allowed after connection closed.
    2014/01/17 16:17:43 - Insert / Update Availabilities.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unable to commit connection(s) rg.pentaho.di.core.exception.KettleDatabaseException:
    2014/01/17 16:17:43 - Insert / Update Availabilities.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error comitting connection
    2014/01/17 16:17:43 - Insert / Update Availabilities.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Communications link failure during commit(). Transaction resolution unknown.
    2014/01/17 16:17:43 - Insert / Update curriculum prefs.0 - Finished processing (I=0, O=0, R=1, W=0, U=0, E=1)
    2014/01/17 16:17:43 - Insert / Update grade levels.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Unable to commit connection(s) rg.pentaho.di.core.exception.KettleDatabaseException:
    2014/01/17 16:17:43 - Insert / Update grade levels.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Error comitting connection
    2014/01/17 16:17:43 - Insert / Update grade levels.0 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Communications link failure during commit(). Transaction resolution unknown.
    2014/01/17 16:17:43 - Insert / Update grade levels.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    2014/01/17 16:17:43 - Insert / Update Availabilities.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
    2014/01/17 16:17:43 - psPeopleToCivi-step2 - psPeopleToCivi-step2
    2014/01/17 16:17:43 - psPeopleToCivi-step2 - psPeopleToCivi-step2
    2014/01/17 16:17:44 - psPeopleToCivi-step2 - psPeopleToCivi-step2
    2014/01/17 16:17:44 - psPeopleToCivi-step2 - psPeopleToCivi-step2
    2014/01/17 16:17:46 - Spoon - The transformation has finished!!
    2014/01/17 16:17:46 - psPeopleToCivi-step2 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Errors detected!
    2014/01/17 16:17:46 - psPeopleToCivi-step2 - ERROR (version 4.4.0-stable, build 17588 from 2012-11-21 16.02.21 by buildguy) : Errors detected!
    2014/01/17 16:17:46 - psPeopleToCivi-step2 - psPeopleToCivi-step2
    2014/01/17 16:17:46 - psPeopleToCivi-step2 - psPeopleToCivi-step2

  2. #2
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You probably want to look at:
    http://dev.mysql.com/doc/refman/5.0/...r_wait_timeout

    Your error says that it was more than 1200 seconds, so even if net_write_timeout is the correct variable to adjust, you haven't set it far enough.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Jun 2013
    Posts
    24

    Default

    Thanks, I'll look at the sys_wait_timeout.

    Regarding net_write_timeout, I was not at all certain that this was the setting to change, but it is what was recommended. It was previously at the default value, 100. And the error does indeed occur about 20 minutes into the transformation. Not knowing how much was enough, and that net_write_timeout at 100 resulted in an error at 20 minutes, I figured that if changing it to 1200 (12x) did NOT help the problem, net_write_timeout was probably not the right solution. It's a complicated transformation, and I really am not sure what event the the timeout was counting from.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    The question has come up many, many, many, MANY times on this forum.
    I usually point people to http://wiki.pentaho.com/display/EAI/MySQL

  5. #5
    Join Date
    Jun 2013
    Posts
    24

    Default

    Thanks Matt, as noted above I tried the net_write_timeout fix and it didn't work. I now see that there's also a tcpKeepAlive setting I will try next.

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Good luck, I feel your pain. This MySQL issue has been around for a long time, Oracle should fix it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.