ItGo.me - 专注IT技术分享

首页 > Exception > The last packet successfully received from the server was 30,002 milliseconds ago

The last packet successfully received from the server was 30,002 milliseconds ago

时间:2016-03-25来源:网友分享 点击:
最近有些服务器出现异常:
org.springframework.dao.RecoverableDataAccessException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,002 milliseconds ago.  The last packet sent successfully to the server was 30,002 milliseconds ago.
### The error may involve 
### The error occurred while setting parameters
### SQL: INSERT INTO
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,002 milliseconds ago.  The last packet sent successfully to the server was 30,002 milliseconds ago.
; SQL []; Communications link failure

The last packet successfully received from the server was 30,002 milliseconds ago.  The last packet sent successfully to the server was 30,002 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,002 milliseconds ago.  The last packet sent successfully to the server was 30,002 milliseconds ago.
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,002 milliseconds ago.  The last packet sent successfully to the server was 30,002 milliseconds ago.
	at sun.reflect.GeneratedConstructorAccessor167.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
	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.sendCommand(MysqlIO.java:2617)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)

Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3166)
	at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3620)
	... 28 more
查了很多资料的解决方法,包括mysql服务器及应该连接池的配置都不会偏差,但依旧出现异常。(环境用了atlas做了读写分离)
mysql> show variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| wait_timeout               | 28800    |
+----------------------------+----------+

dbcp连接池配置如下:
jdbc.maxActive=64
jdbc.maxIdle=10
jdbc.validationQuery=select 1;
jdbc.testOnBorrow=true
jdbc.testWhileIdle=true

网上大部分说的解放方法:
      Mysql服务器默认的“wait_timeout”是8小时(也就是默认的值默认是28800秒),也就是说一个connection空闲超过8个小时,Mysql将自动断开该connection,通俗的讲就是一个连接在8小时内没有活动,就会自动断开该连接。
而连接池却认为该连接还是有效的(因为并未校验连接的有效性),当应用申请使用该连接时,就会导致上面的报错。
 
      wait timeout的值可以设定,但最多只能是2147483,不能再大了。也就是约24.85天。
      修改方法:MySQL通过my.ini 在
      # The TCP/IP Port the MySQL Server will listen on
      port=3306下面添加
      # this is myown dinifition for mysql connection timeout
      wait_timeout=31536000
      interactive_timeout=31536000
 
      以上是在网上找到的解决方法,但是一般连接的有效时间不用修改
============
后来发现是haproxy超时设置问题,设为30秒了,增加值后解决了。因此可验证该错误是连接服务器超时导致,上面的方法也可行。


官方的文档.会不会是net_read_timeout,因为这个时间我们设置30s,与报错的时间接近(
received from the server was 30,002 milliseconds ago
   The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.

    获取MySQL连接是多次握手的结果,除了用户名和密码的匹配校验外,还有IP->HOST->DNS->IP验证,任何一步都可能因为网络问题导致线程阻塞。为了防止线程浪费在不必要的校验等待上,超过connect_timeout的连接请求将会被拒绝。

    即使没有网络问题,也不能允许客户端一直占用连接。对于保持sleep状态超过了wait_timeout(或interactive_timeout,取决于client_interactive标志)的客户端,MySQL会主动断开连接。

    即使连接没有处于sleep状态,即客户端忙于计算或者存储数据,MySQL也选择了有条件的等待。在数据包的分发过程中,客户端可能来不及响应(发送、接收、或者处理数据包太慢)。为了保证连接不被浪费在无尽的等待中,MySQL也会选择有条件(net_read_timeout和net_write_timeout)地主动断开连接。



No live SolrServers available to handle this request

org.apache.solr.client.solrj.SolrServerException: No live SolrServers available to handle this request:[http://192.168.0.2:8983/solr/test, http://192.168.0.3:8983/solr/test, http://192.168.0.4:8983/solr/test]...

Column count doesn't match value count at row 1原因及解决

Cause: java.sql.SQLException: Column count doesn't match value count at row 1...

The last packet successfully received from the server was 30,002 milliseconds ago

The last packet successfully received from the server was 30,002 milliseconds ago. The last packet sent successfully to the server was 30,002 milliseconds ago. Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 by...

The last packet successfully received from the server was 30,002 milliseconds ago. The last packet sent successfully to the server was 30,002 milliseconds ago. Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 by
------分隔线----------------------------