I have a process which runs via Pentaho on a Unix server against MySQL 5.6.19. and using 5.1.32 of the MySQL jdbc driver. We actually found this driver helped with this error in some cases compared to 5.1.26
The process is an extremely long running heavy load analytical process. Last night I had a failure, and I can see from the system stats that there was a large amount of "iowait" on the system which started ~15mins before this failure.
The actual error is pasted below. I've lived with this error over the years and usually it is simply due to the net_write_timeout. So that has already been set at 86,400 or 24 hours. For good measure i set net_read_timeout to the same. I also had a situation where after 8 hours we hit the "wait_timeout" with exactly the same error so that is now also at 86,400.
So I don't really understand the error. Last packet sent was 15 seconds ago, last packet received was 142ms ago. so this cannot be a timeout? It must be something else? Yet googling around for this error every time people say "its the timeout". I've also set tcpKeepAlive=true for good measure.
The MySQL and Pentaho process are both working on the same local box. No errors in /var/log/messages or in the MySQL error log.
It's hard to know if this should be posted here, or on the sysadmin site, or elsewhere.
Here's the full error:
org.pentaho.di.core.exception.KettleDatabaseException: Couldn't get row from result set Communications link failure
The last packet successfully received from the server was 142 milliseconds ago. The last packet sent successfully to the server was 14,468,141 milliseconds ago.
at org.pentaho.di.core.database.Database.getRow(Database.java:2420)
at org.pentaho.di.core.database.Database.getRow(Database.java:2389)
at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:145)
at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 142 milliseconds ago. The last packet sent successfully to the server was 14,468,141 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:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1137)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3697)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3586)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4131)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:925)
at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2033)
at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:408)
at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:387)
at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7008)
at org.pentaho.di.core.database.Database.getRow(Database.java:2408)
... 4 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 45 bytes, read 4 bytes before connection was unexpectedly lost.
at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3143)
at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3653)
... 12 more