MySQL5.7 Aborted connection

开发环境数据库升级到MySQL5.7.21 后，日志中断断续续出现[Note] Aborted connection。
参考官方文档，分析了一下https://dev.mysql.com/doc/refman/5.7/en/communication-errors.html


发现这个描述跟遇到的情况比较类似，即因为客户端空闲时间超过了wait_timeout或interactive_timeout超时导致。
If a client successfully connects but later disconnects improperly or is terminated, 
the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log.
The cause can be any of the following:
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. See Section 5.1.5, “Server System Variables”.
The client program ended abruptly in the middle of a data transfer.


mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)


错误日志
tail -f  error.log
[Note] Aborted connection 10685 to db: 'zabbix' user: 'monitor' host: '192.168.xxx.xxx' (Got timeout reading communication packets)


当日志中出现错误时，Aborted_clients数量也在增加
mysql> show status like 'Aborted%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Aborted_clients  | 1964  |
| Aborted_connects | 69    |
+------------------+-------+
2 rows in set (0.01 sec)


通过如下SQL预测下次出现Aborted connection的时间
select sysdate(), id,SUBSTRING_INDEX(host,':',1) host,time,     DATE_ADD(sysdate()  ,INTERVAL @@wait_timeout-time   second)  'Aborted Time' 
 from  information_schema.PROCESSLIST  where id>100 and time>1000  order by time desc ,id;
+---------------------+-------+----------------+------+---------------------+
| sysdate()       | id  | host        | time | Aborted Time     |
+---------------------+-------+----------------+------+---------------------+
| 2018-03-30 16:10:52 | 10786 | 192.168.x.191  | 1800 | 2018-03-30 16:10:52 |
| 2018-03-30 16:10:52 | 10787 | 192.168.x.117  | 1800 | 2018-03-30 16:10:52 |
| 2018-03-30 16:10:52 | 10748 | 192.168.x.221  | 1735 | 2018-03-30 16:11:57 |
| 2018-03-30 16:10:52 | 10692 | 192.168.x.221  | 1686 | 2018-03-30 16:12:46 |
| 2018-03-30 16:10:52 | 10777 | 192.168.x.221  | 1102 | 2018-03-30 16:22:30 |
+---------------------+-------+----------------+------+---------------------+
5 rows in set (0.01 sec)


检查error.log发现日志出现的时间与SQL中得到的Aborted Time吻合.
2018-03-30T16:10:52.661416+08:00 10786 [Note] Aborted connection 10786 to db: 'db01' user: 'apps' host: '192.168.0.191' (Got timeout reading communication packets)
2018-03-30T16:10:52.668516+08:00 10787 [Note] Aborted connection 10787 to db: 'db02' user: 'apps' host: '192.168.0.117' (Got timeout reading communication packets)

2018-03-30T16:11:57.564463+08:00 10748 [Note] Aborted connection 10748 to db: 'db03' user: 'apps' host: '192.168.x.221' (Got timeout reading communication packets)

解决办法：
mysql> set global log_error_verbosity=2;
Query OK, 0 rows affected (0.00 sec)


参考文档：
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_log_error_verbosity