CommunicationsException：通信链路故障 [英] CommunicationsException: Communications link failure

问题描述

我用java从Mysql查询一些记录。但是在一个持续时间的一些查询中，我遇到了使查询失败的问题，但在其他问题上，它查询成功。接下来是错误消息：

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_pa​​cket = 128 * 1024 * 1024 in /etc/my.conf


• 将？autoReconnect = true& failOverReadOnly = false& maxReconnects = 10 添加到我的连接网址

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

但没有任何反应。

我的环境是：

• Mysql： 5.5.3-m3-log源代码分发


• Java：1.6.0_16


• Jdk：HotSpot（TM）64位服务器VM（版本14.2） -b01，混合模式）


• JDBC：mysql-connector-java-5.1.18

• 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

推荐答案

问题解决了。
这是因为结果太大了。在我的查询中，我使用了默认游标，即客户端游标。这意味着，SELECT的整个结果记录集将返回到客户端（应用程序），并在那里完成分页。所以总结果集太大，使jdbc客户端内存不足。
解决方案是：

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. 将useCursorFetch = true添加到JDBC URL配置参数


2. call statement.setFetchSize（100）

您可以从以下网址阅读更多详细信息： http://wiki.gxtechnical.com/commwiki/servlet/hwiki?Client% 20和％20server％20cursors％20-％20使用％20MySQL

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

这篇关于CommunicationsException：通信链路故障的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！