CommunicationsException: Communications link failure

Question

I used java to query some records from Mysql. But in some querys of one duration, i meet a problem which make query failed, but in others , it query successful. The error message is next:

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

The last packet successfully received from the server was 90 milliseconds ago.  The last packet sent successfully to the server was 1,674 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:411)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1116)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3090)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2979)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3520)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)
    at com.mysql.jdbc.MysqlIO.nextRow(MysqlIO.java:1433)
    at com.mysql.jdbc.MysqlIO.readSingleRowSet(MysqlIO.java:2924)
    at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:477)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2619)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1788)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2209)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1521)
    ......
    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)
    at org.eclipse.jdt.internal.jarinjarloader.JarRsrcLoader.main(JarRsrcLoader.java:58)
Caused by: java.io.IOException: Packets received out of order
    at com.mysql.jdbc.MysqlIO.readRemainingMultiPackets(MysqlIO.java:3152)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3077)
    ... 23 more

I have tried some method, such like:

• set max_allowed_packet = 128 * 1024 * 1024 in /etc/my.conf
• add ?autoReconnect=true&failOverReadOnly=false&maxReconnects=10 to my connection url

but nothing happens.

My environments is:

• Mysql: 5.5.3-m3-log Source distribution
• Java: 1.6.0_16
• Jdk: HotSpot(TM) 64-Bit Server VM (build 14.2-b01, mixed mode)
• JDBC: mysql-connector-java-5.1.18

Answer 1

The problem are solved. It is because the result is too huge. In my query , i used the default cursor, which is client-side cursors, This means, the whole resultant recordset of a SELECT is returned to the client (application) and the paging is done there. So the total result set is too big and make jdbc client out of memory. The solution is that:

1. add "useCursorFetch=true" to JDBC URL configuration parameters
2. call statement.setFetchSize(100)

You can read more detail from : http://wiki.gxtechnical.com/commwiki/servlet/hwiki?Client%20and%20server%20cursors%20-%20using%20MySQL