# 如何解决Mysql异常:Communications link failure

# 问题现象

MySQL数据加工或者计算报表时偶尔会报下列异常。

异常1:

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.cj.protocol.FullReadInputStream.readFully(FullReadInputStream.java:67)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:63)
	at com.mysql.cj.protocol.a.SimplePacketReader.readHeader(SimplePacketReader.java:45)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:556)
 [wrapped] com.mysql.cj.exceptions.CJCommunicationsException: Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
	at jdk.internal.reflect.GeneratedConstructorAccessor117.newInstance(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
	at com.mysql.cj.protocol.a.NativeProtocol.readMessage(NativeProtocol.java:562)
	at com.mysql.cj.protocol.a.NativeProtocol.readServerCapabilities(NativeProtocol.java:514)
	at com.mysql.cj.protocol.a.NativeProtocol.beforeHandshake(NativeProtocol.java:404)
	at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1447)

异常2:

10:45:05.709
正在执行导入,已写入行数:0
10:45:15.861
load data local infile '' ......
10:57:43.397
正在执行导入,已写入行数:1024000
11:00:28.874
Communications link failure

The last packet successfully received from the server was 913,010 milliseconds ago.  The last packet sent successfully to the server was 165,478 milliseconds ago.异常堆栈: 
java.net.SocketException: 断开的管道 (Write failed)
	at java.base/java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.base/java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:110)
	at java.base/java.net.SocketOutputStream.write(SocketOutputStream.java:150)
	at java.base.security.ssl.SSLSocketOutputRecord.deliver(SSLSocketOutputRecord.java:341)
	at java.base.security.ssl.SSLSocketImpl$AppOutputStream.write(SSLSocketImpl.java:1186)
	at java.base/java.io.BufferedOutputStream.write(BufferedOutputStream.java:123)

# 问题原因

  1. MySQL数据库服务重启了,而应用的数据库连接池缓存还是旧的数据库连接。

  2. net_read_timeoutnet_write_timeout 参数默认配置的时间过短,一般发生在数据加工数据迁移场景下。以两个MySQL使用数据加工迁移数据为例说明,数据加工抽取数据采用MySQL load data 加管道流的方式实现,由于源头获取数据被卡住了,在往目标库写入数据时超过MySQL net_read_timeout参数的限制,该参数默认为30秒,即30秒MySQL没有从jdbc连接中获取数据,就会自动断掉连接。上述异常说明如下:

    1. The last packet sent successfully to the server was 165,478 milliseconds ago ,表示在165秒以前成功传输过数据,往MySQL写入数据连接是30秒,而错误是在165秒发生,是因为在这165秒之间没有写入数据了,在165秒以后待管道的源表在次获取数据后进行写入时,这时连接超时抛出了异常。
    2. The last packet successfully received from the server was 913,010 milliseconds ago. 表示建立连接执行load data 后,MySQL给jdbc客户端的响应;从上面的异常日志看出,10:45:15开始导入,异常11:00:28抛出,预计913秒左右。

注意:

MySQL默认配置了 wait_timeout=28800 ,8个小时连接没有使用会自动回收掉,回收后连接池不知道继续使用被回收的连接也会导致上述异常,但在SuccBI中默认每半小时检查一下连接的有效性,MySQL数据库自动回收空闲连接应该不存在。

# 排查步骤

连接池有效性检查

配置连接池的有效性,参考数据源管理:数据源管理。如果是配置默认连接池,修改工作目录下的jdbc.conf中的testConnectionOnCheckout参数为true,参考如下:

{
    "driver": "com.mysql.cj.jdbc.Driver",
    "dbType": "MySQL",
    "url": "jdbc:mysql://xxxxxxx:3361/whhsjc?useUnicode=true&characterEncoding=utf8&allowLoadLocalInfile=true",
    "user": "xxxxx",
    "password": "xxxxx",
    "testConnectionOnCheckout": "true"
}

MySQL timeout参数检查

  1. 查看当前MySQL数据库的超时参数,在数据库中执行 show global variables like '%timeout%'

    VARIABLE_NAME	                    VALUE
    connect_timeout	                    10
    delayed_insert_timeout	            300
    have_statement_timeout	            YES
    innodb_flush_log_at_timeout    	    1
    innodb_lock_wait_timeout	        2
    innodb_rollback_on_timeout	        OFF
    interactive_timeout	                28800
    lock_wait_timeout	                31536000
    mysqlx_connect_timeout	            30
    mysqlx_idle_worker_thread_timeout	60
    mysqlx_interactive_timeout	        28800
    mysqlx_port_open_timeout	        0
    mysqlx_read_timeout	                30
    mysqlx_wait_timeout	                28800
    mysqlx_write_timeout	            60
    net_read_timeout	                30
    net_write_timeout	                60
    rpl_stop_slave_timeout	            31536000
    slave_net_timeout	                60
    wait_timeout	                    28800
    
  2. my.cnf(windows为my.ini)中修改net_read_timeoutnet_write_timeout 参数值,修改Mysql配置文件参考Mysql安装配置

    net_read_timeout=1800
    net_write_timeout=1800
    

上述参数单位为秒,修改后需要重启MySQL服务才能生效。

说明:

  1. net_read_timeout 表示MySQL服务器从客户端读取数据的网络超时,即客户端向服务器提交数据时,等待多少秒仍未执行成功时自动断开连接
  2. net_write_timeout 表示MySQL服务器往客户端写数据的网络超时,即表示客户端从服务器端读取数据,等待多少秒仍未执行成功时自动断开连接
是否有帮助?
0条评论
评论