Packets received out of order with large ResultSet from MySQL, JDBC

Question

I have a problem, which I have not been able to resolve for a couple of days now.. So, i'm trying to list a large resultset row by row with the solution, cited multiple times on the internets, including stackoverflow:

Statement stmt = readOnlyConn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(Integer.MIN_VALUE);

and it seems to work. At least for some time (it writes out the lines into a text file) until I get the error below. I have googled my eyes out trying to find what could be causing this, tried some mysql server tweaks, nothing works. Can anyone please help me? Everyone keeps reporting splendid results with the solution above, but it seems to hate me in particular.. I would greatly appreciate any help.

    java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 15 milliseconds ago.  The last packet sent successfully to the server was 6,239 milliseconds ago.
    at gblaster.main.lambda$main$1(main.java:134)
    at gblaster.main$$Lambda$3/792791759.accept(Unknown Source)
    at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1359)
    at java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:580)
    at gblaster.main.main(main.java:124)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:483)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 15 milliseconds ago.  The last packet sent successfully to the server was 6,239 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1129)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3720)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4160)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:928)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:2053)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:3554)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:491)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3245)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2413)
    at com.mysql.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:1500)
    at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:857)
    at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2313)
    at db.mysql.GMySQLConnector.loadAllOrfsForGenomeId(GMySQLConnector.java:296)
    at gblaster.deploy.Deployer.unloadORFsForGenomeToFile(Deployer.java:124)
    at gblaster.main.lambda$main$1(main.java:128)
    ... 9 more
Caused by: java.io.IOException: Packets received out of order
    at com.mysql.jdbc.MysqlIO.readRemainingMultiPackets(MysqlIO.java:3782)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3707)
    ... 23 more

score 1 · Accepted Answer · answered Jun 21 '14 at 04:17

Your code is difficult to understand because Integer.MIN_VALUE is negative,

stmt.setFetchSize(Integer.MIN_VALUE);

I think you'll find it works much better with

stmt.setFetchSize(100); // <-- I also bet this works better then 1.

Finally, the Statement.setFetchSize(int) javadoc says (emphasis added),

Throws:

SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

So you should be getting a SQLException with your code.

Packets received out of order with large ResultSet from MySQL, JDBC

1 Answers1