Processing on MySQL query taking too long and throwing "Communications link failure"

Question

I issued a query using a JDBC connection:

Connection conn = null
Class.forName("com.mysql.jdbc.Driver")
conn = DriverManager.getConnection(dbHost, dbUser, dbPass)

s = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
s.setFetchSize(Integer.MIN_VALUE);
r = s.executeQuery (MY_SELECT_QUERY);
while(...) { 
  processResultSet(r);
}

The while runs for a long time processing the ResultSet. After around 1 hour i got the exception:

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

The last packet successfully received from the server was 74 milliseconds ago. The last packet sent successfully to the server was 4,351,980 milliseconds 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:407) 
   at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)

   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3082) 
   at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2968) 
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3516) 
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:931) 
   at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1429) 
   at com.mysql.jdbc.RowDataDynamic.nextRecord(RowDataDynamic.java:416) 
   at com.mysql.jdbc.RowDataDynamic.next(RowDataDynamic.java:395) 
   at com.mysql.jdbc.ResultSetImpl.next(ResultSetImpl.java:7171) 
   at com.busk.indexer.AbstractIndexerClient.indexResultSet(AbstractIndexerClient.scala:34)

   at com.busk.indexer.ContinousIndexer.startIndexingLoop(ContinousIndexer.scala:18)

   at com.busk.indexer.IndexerServer$.main(IndexerServer.scala:95) 
   at com.busk.indexer.IndexerServer.main(IndexerServer.scala) 
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

   at java.lang.reflect.Method.invoke(Method.java:597)

I've googled around, but almost all errors are related to connection pooling, and using a connection for more than 8h (the default wait_timeout mysql setting). In my case, I'm in the middle of ResultSet processing and I get this error.

Does it happen for a specific query or for every one? Can you try with something like 'select 1'. Maybe it takes too long to execute the query on the DB side? — Eugene Retunsky
I have only this query on my system (with different parameters). I know the results are already being streamed because my application is already processing the ResultSet (I see through the application logs). The query is definetely executed by the DB and it started to send results, but at some point of sending ResultSet items it stops. — Felipe Hummel

Eugene Retunsky Eugene Retunsky · Accepted Answer · 2012-03-30T20:09:12

If it happens because you have a lot of data in the result set, I would suggest to paginate the response with "LIMIT s, m" (where s - start, and m - max records - both integers). Then process this data in a loop with portions of 1000 records at a time:

boolean finished = false;
int start = 1;
int max = 1000;
do {
   r = s.executeQuery (MY_SELECT_QUERY + " LIMIT " + start + ", " + max);
   finished = // if r returned less than M records
   while(...) { 
     processResultSet(r);
   } 
   start += // number of returned records;
} while (!finished);

Of course, 1000 is an arbitrary number, you can play around and find what is the optimal max size for you query.

UPDATE: If processing of each record takes a long time, then store records in a list and iterate through them when all data is fetched from the database.

Processing on MySQL query taking too long and throwing "Communications link failure"

1 Answers