MySQL错误日志Got an error reading communication packet

问题

经常收到客户关于通信故障错误的问题—客户面临间歇性的”Got an error reading communication packet”错误
这里分析这个错误出现的原因，以及如何解决这个问题。

官方解释

首先，当通信故障错误出现时，MySQL的状态变量Aborted_clients和Aborted_connects的计数会增加。这两个状态变量描述了由于客户端没有正确关闭连接而导致中断的连接数以及那些尝试登录MySQL失败的连接数量（分别）。两个错误出现的可能原因很多
下面看看官网怎么说：
Aborted_connects：
If a client is unable even to connect, the server increments the Aborted_connects status variable.

• A client attempts to access a database but has no privileges for it.
  #客户端没有权限但是尝试访问MySQL数据库
• A client uses an incorrect password.
  #客户端输入的密码有误。
• A connection packet does not contain the right information.
  #连接包不包含正确信息
• takes more than connect_timeout seconds to obtain a connect packet.
  #超过连接时间限制，主要是这个系统变量connect_timeout控制（mysql默认是10s，基本上，除非网络环境极端不好，一般不会超时。）

Aborted_clients：
lIf a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable

• The client program did not call mysql_close() before exiting…
  #客户端没有进行关闭
• The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server.
  #客户端睡眠时间超过了wait_timeout或interactive_timeout秒，而没有向服务器发出任何请求。
• The client program ended abruptly in the middle of a data transfer.
  #客户端程序在数据传输过程中突然终止。

Aborted_connects OR Aborted_clients：
Other reasons for problems with aborted connections or aborted clients:

• the max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld
  #max_allow_packet设置过小
• Use of Ethernet protocol with Linux, both half and full duplex. Some Linux Ethernet drivers have this bug
  #Linux以太网驱动程序有这个bug
• A problem with the thread library that causes interrupts on reads.#线程库中导致读取中断的问题。
• Badly configured TCP/IP. #tcp/iip 配置信息混乱
• Faulty Ethernets, hubs, switches, cables, and so forth. This can be diagnosed properly only by replacing hardware.
  #故障的以太网、集线器、交换机、电缆等等

https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html

分析

a.密码错误&权限错误：

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like 'Abort%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 0     |
| Aborted_connects | 0     |
+------------------+-------+
2 rows in set (0.00 sec)
密码错误登录：
复制

Error.log:

状态信息：

##权限错误
无权限登录：

Error.log:

b.传输包错误
A connection packet does not contain the right information ：

A connection packet does not contain the right information ：
复制

c.程序未调用close
客户端程序在退出之前未调用mysql_close（）正确关闭MySQL连接。

####python代码####
#!/usr/bin/python
# -*- coding: UTF-8 -*-
import pymysql
# 打开数据库连接
db = pymysql.connect(host='192.168.244.130', port=3390, database='sbtest',
                     charset='utf8',user='dbadmin', password='123456')
# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()
# 使用 execute()  方法执行 SQL 查询
#cursor.execute("SELECT VERSION()")
cursor.execute("SELECT * FROM t1;")
# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()
print("Database version : %s " % str(data))
# 关闭数据库连接
#db.close()
复制

Error log信息：

d.timeout设置
先看看timeout相关有哪些mysql参数

mysql>show variables like '%timeout%';
复制

主要是4个timeout参数：
connect_timeout在握手认证阶段（authenticate）起作用，interactive_timeout 和wait_timeout在连接空闲阶段（sleep）起作用，
而net_read_timeout和net_write_timeout则是在连接繁忙阶段（query）或者网络出现问题时起作用。

先看看变量interactive_timeout 和wait_timeout设置为4秒

mysql> set global interactive_timeout=4;
Query OK, 0 rows affected (0.00 sec)
复制

e.max_allowed_packet设置
将全局系统变量interactive_timeout 和wait_timeout设置为4秒

mysql> set global max_allowed_packet=1024;
Query OK, 0 rows affected, 1 warning (0.00 sec)

复制

###但以下脚本并发多个的时候，发现
###python 脚本如下：

f.网络延迟
MySQL的系统变量net_read_timeout 和 net_write_timeout 的值，看看是否会减少错误的数量，net_read_timeout是很少出现的异常，除非你的网络环境实在是太糟糕了，尝试调整这些值，因为在大多数情况下，会生成一个查询并将其作为单个数据包发送到服务器，并且应用程序无法切换到执行其他操作，而将服务器保留作为部分接收的查询。

netem通过模拟广域网的特性为测试协议提供网络仿真功能。当前版本模拟可变延迟，丢失，重复和重新排序.
##通过python代码链接mysql服务器发现error日志记录如下：

[root@ss30 ~]# tc qdisc add dev ens33 root netem delay 90000ms
复制

从上述情况分析看来：”Got an error reading communication packet”现象

1.客户端已经成功连接，但是异常终止了（可能与未正确关闭连接有关系）
2.客户端休眠时间超过了系统变量wait_timeout或interactive_timeout的定义值（最终导致
连接休眠的时间超过系统变量wait_timeout的值，然后被MySQL强行关闭）
3.客户端异常中断或查询超出了max_allowed_packet值。

MySQL内部，大量的MySQL连接处于休眠状态并休眠了数百秒是应用程序在完成工作后没有关闭连接的症状之一，它们依靠wait_tiemout系统变量来关闭连接。 建议修改应用程序逻辑，在操作结束后正确关闭连接。

mysql> select @@log_warnings;
+----------------+
| @@log_warnings |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)
mysql> set global log_warnings=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@log_warnings;
+----------------+
| @@log_warnings |
+----------------+
|              0 |
+----------------+
1 row in set (0.00 sec)
复制

官方文档对log_warnings的解释:
The server logs messages about statements that are unsafe for statement-based logging if the value is greater than 0. Aborted connections and access-denied errors for new connection attempts are logged if the value is greater than 1
https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_log-warnings

案例1

制作三月份运维月报过程中，发现标签数据库从库MySQL连接异常率较高，查看错误日志后发现大量Abort Connection (Got an error reading communication packets)，记录的mysql用户为baihang，IP为HAproxy的IP地址，分布较为均匀但无明显时间规律，如图（图1为Nginx日志，图2为MySQL错误日志）：

业务流程:

首先，如上图1.2所示，MySQL错误日志中打印的Got an error reading问题表示出：客户端已经成功连接，但是异常终止。\问题点在tomcat，HAproxy或MySQL三者之中，怀疑此问题由三者之中的超时处理机制导致，下面为三者的超时参数设置及大致解释。

Tomcat 中的超时参数:

hummer.datasource.hikari.idle-timeout
#mySql连接-idle状态的最大时长
hummer.datasource.hikari.max-lifetime
#mySql连接-生命时长
hummer.datasource.hikari.connection-timeout
#mySql连接-超时时间
复制

HAproxy 中的超时参数:

timeout queue 1m
#一个请求在队列里的超时时间
timeout connect 10s
#连接超时
timeout client 1m
#客户端超时
timeout server 1m
#服务器端超时
timeout check 10s
#检测超时
maxconn 65535
#每个进程可用的最大连接数
复制

MySQL 中的超时参数:

connect_timeout
#连接过程中握手的超时时间
wait_timeout & interactive_timeout
#不活跃的连接超时时间
net_read_timeout  &  net_write_timeout
#在网络条件不好的情况下起作用
复制

处理过程：

已进行的配置调整及结果反馈
MySQL参数调优，优化mysql运行参数，将max_allowed_packet调整为1G，slave_max_allowed_packet调整为1G，slave_pending_jobs_size_max调整为256M。但从21日前后的MySQL错误日志中，不能看出明显差异，可以初步认为与此三项无关，另因错误日志中出现Got an error reading…的分布较为均匀而非集中式大量出现，且MySQL wait_timeout & interactive_timeout均设置为8小时，推断问题点非MySQL。

通过修改Tomcat后端连接IP，将Tomcat直接连接在MySQL数据库前，MySQL错误日志中则不会打印Got an error reading…相关信息。初步推断问题点大概率为HAproxy。

通过注释掉HAproxy的参数timeout client 1m (客户端超时)和timeout server 1m (服务器端超时)，此问题不再出现。附相关参数官方解释如下：

The inactivity timeout applies when the client is expected to acknowledge or send data. In HTTP mode, this timeout is particularly important to consider during the first phase, when the client sends the request, and during the response while it is reading data sent by the server. That said, for the first phase, it is preferable to set the “timeout http-request” to better protect HAProxy from Slowloris like attacks.

This parameter is specific to frontends, but can be specified once for all in “defaults” sections. This is in fact one of the easiest solutions not to forget about it. An unspecified timeout results in an infinite timeout, which is not recommended. Such a usage is accepted and works but reports a warning during startup because it may results in accumulation of expired sessions in the system if the system’s timeouts are not configured either.

大意为：当预期客户机确认或发送数据时，应用不活动超时。在HTTP模式中，在第一阶段(客户机发送请求时)和在响应期间(读取服务器发送的数据时)考虑这个超时特别重要。也就是说，对于第一阶段，最好设置“timeout http-request”，以便更好地保护HAProxy免受类似慢懒虫的攻击。

这个参数是特定于前端的，但可以在“默认值”部分一次性指定。这实际上是最简单的解决方案之一，不要忘记它。未指定超时将导致无限超时，不建议这样做。这样的用法可以接受并正常工作，但是在启
动期间会报告一个警告，因为如果系统的超时也没有配置，则可能导致系统中过期会话的累积。

其他案例：
MySQL Group Replication集群，还需要tracking网络异常等错误

解决方案

1.在Tomcat中添加一个类似心跳的查询操作，使连接池内所有的空闲连接周期性的发送测试SQL语句。
优点：解决彻底；
缺点：但此种做法要修改所有连接HAproxy的应用端的配置，且修改后也许会有其他连锁反应，比如目前回收连接资源的策略和方式是否会受到影响等。

2.在HAproxy配置文件中注释掉参数 timeout client（这个超时时间不是指连接过程的超时时间，而是指连接上以后，多少时间内没有心跳或操作，超过这个时间就认为超时，然后断开连接）。
优点：操作方便，需要改动的点较少；
缺点：参见2.2.4。

3.忽略MySQL错误日志中报出的Abort Connection。
优点：无需任何操作，不会对现有的配置产生任何影响
缺点：错误日志中每天打印上千条Abort Connection的记录，且zabbix监控可以感知此记录，现有的连接成功率计算方式会受到此信息的干扰。

总结：

但问题出现的时候，可以先看看 观察一下show global status like ‘abort%’;Aborted_clients 和 Aborted_connects 是否是变动剧烈。
在看看对应的应用的日志