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)地主动断开连接。



org.apache.hadoop.mapred.SafeModeException: JobTracker is in safe mode解决办法

ERROR security.UserGroupInformation: PriviledgedActionException as:hp cause:org.apache.hadoop.ipc.RemoteException: org.apache.hadoop.mapred.SafeModeException: JobTracker is in safe mode...

org.apache.http.TruncatedChunkException: Truncated chunk解决方法

org.apache.http.TruncatedChunkException: Truncated chunk ( expected size: 8003; actual size: 7434) at org.apache.http.impl.io.ChunkedInputStream.read(ChunkedInputStream.java:186) at org.apache.http.conn.EofSensorInputStream.read(EofSensorInputStre...

org.jsoup.UnsupportedMimeTypeException: Unhandled content type. Must be text解决

org.jsoup.UnsupportedMimeTypeException: Unhandled content type. Must be text/*, application/xml, or application/xhtml+xml. Mimetype=application/json;charset=UTF-8...

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
------分隔线----------------------------