Aborted connection 1055898 to db: ' xxx ' User: ' yyy ' host: ' xxx.xxx.xxx.xxx ' (Got timeout reading communication packets)

In the MySQL error log, a large number of similar information is found: (MySQL 5.7.18)

[Note] Aborted connection 1055898 to db: ' xxx ' User: ' yyy ' host: ' xxx.xxx.xxx.xxx ' (Got timeout reading communication packets)

In this aborted connection case, MySQL increases the value of the Aborted_clients status counter. This also means the following issues:

(1) The client is properly connected, but is terminated abnormally (the program may not have a graceful shutdown connection)
(2) The client sleep time exceeds the value of Wait_timeout, or interactive_timeout (this causes the connection to be forcibly closed by MySQL)
(3) Client exception terminal, or query exceeds Max_allowed_packet value

mysql> show variables like ‘%max_allowed_packet%‘;
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 16777216   |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

Of course, there may be other causes. Frankly speaking, abnormal terminal is difficult to diagnose, it may be related to network, firewall. Can be considered from the following aspects:

1. If a large number of connection processes are in the sleep state for a long time, it means that the application does not close the database connection correctly and in a timely manner. It is strongly recommended that you close the database connection properly in your application, otherwise you will need to rely on MySQL's wait_timeout settings to close the connection.

2. It is recommended to check the value of the max_allowed_packet to ensure that the value is set reasonably so that the client does not receive a "Packet too large" message prompt. If the settings are unreasonable, the connection is interrupted abnormally.

3. It is recommended to focus on the number of time_wait processes. If Netstat discovers that a large number of connections are in the TIME_WAIT state, the recommended application side adjusts the connection shutdown issue.

#               netstat -ano|grep TIME_WAIT
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55586      TIME_WAIT   timewait (32.97/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55367      TIME_WAIT   timewait (27.82/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55776      TIME_WAIT   timewait (37.09/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:56505      TIME_WAIT   timewait (54.61/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55553      TIME_WAIT   timewait (31.94/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:56643      TIME_WAIT   timewait (57.73/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55221      TIME_WAIT   timewait (23.70/0/0)
tcp        0      0 xxx.xxx.xxx.xxx:10054       xxx.xxx.xxx.xxx:55920      TIME_WAIT   timewait (41.18/0/0)

4. Ensure that transactions are submitted correctly and in a timely manner.

5. Ensure that the application does not have an abnormal interrupt connection, such as PHP if you set the max_execution_time=5, even if you increase the value of connect_timeout will not be effective. Other programming languages will have similar problems.

6. Check the DNS configuration for latency issues. Check that Skip_name_resolve is configured at the same time and use IP to authenticate the host instead of using the host name. After setting this parameter, use IP to authenticate the host instead of using the host name. With this parameter, the host column in the MySQL authorization table must be an IP address or localhost.

7. If you are Percona, you can open the audit log.

8. Increase the value of Net_read_timeout, Net_write_timeout, and see if the error has occurred. Net_read_timeout rarely cause errors unless the network environment is very poor.

The connection to the exception terminal is due to the connection not being normal. The server side does not cause a connection abort unless there is a network problem on the client/server side. But this is also caused by the network, not the server side of the problem. Network problems can be viewed with tools such as: tcpdump,netstat-s

To get back to the problem itself, first look at the MySQL parameter settings:

mysql> show variables like ‘%timeout%‘;
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| interactive_timeout         | 1800     |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| wait_timeout                | 1800     |
+-----------------------------+----------+
mysql> show global variables like ‘%log_warning%‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings  | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> 

If the value of log_warnings is greater than 1,mysql writes similar information to the error log:

[Warning] Aborted connection 305628 to db: ‘db‘ user: ‘dbuser‘ host: ‘hostname‘ (Got an error reading communication packets)
[Warning] Aborted connection 305627 to db: ‘db‘ user: ‘dbuser‘ host: ‘hostname‘ (Got an error reading communication packets)

If you do not want to record this information in the log, you can modify the value of log_waring:

Mysql>set @ @global log_warning=1;

Aborted connection 1055898 to db: ' xxx ' User: ' yyy ' host: ' xxx.xxx.xxx.xxx ' (Got timeout reading communication packets)