5

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
zhouzuan2k
  • 157
  • 2
  • 8
  • In the operation, i used `group_concat` and my concat string is very long. And i also try the query using php, it works well. When i set `group_concat_max_len` < 32M, it will cut some data by was cut by GROUP_CONCAT() and return success. But if group_concat_max_len > 32M, it failed. – zhouzuan2k Oct 25 '12 at 06:36
  • possible duplicate of [MySQL-JDBC: Communications Link Failure](http://stackoverflow.com/questions/2121829/mysql-jdbc-communications-link-failure) – CloudyMarble Jun 13 '13 at 09:44

2 Answers2

6

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

zhouzuan2k
  • 157
  • 2
  • 8
0

This might happen when you try to commit with stale connection. Try checking for the connection state before doing the commit.

Slowcoder
  • 2,060
  • 3
  • 16
  • 21
  • When the connection is valid, the operation fails too. Indeed, although i do this operation just after the connection opens, it also fails. – zhouzuan2k Oct 25 '12 at 06:31