Analysis of MySQL Aborted connection warning log

Preface:

Sometimes, the session connected to MySQL often exits abnormally, and you will see an alarm of the type "Got an error reading communication packets" in the error log. In this article, we will discuss the possible causes of this error and how to avoid it.

1. Status variables Aborted_clients and Aborted_connects

First, let's understand the meaning of the two status variables Aborted_clients and Aborted_connects. When a session exits abnormally, these two status values ​​will change. According to the official documentation, the summary is as follows:

Possible reasons for the increase of Aborted_connects status variable:

1. The client attempted to access a database but did not have permission to access the database.
2. The client used an incorrect password.
3. The connection packet does not contain the correct information.
4. It took longer than connect_timeout seconds to obtain a connection packet.

Possible reasons for the increase of Aborted_clients status variable:

1. The client program did not call mysql_close() before exiting.
2. The client has been sleeping for more than the number of seconds specified by the wait_timeout or interactive_timeout parameters.
3. The client program suddenly terminated during data transfer.

In simple terms, if the database session fails to connect to the database normally, the Aborted_connects variable will increase. The database session has been connected to the database normally but failed to exit normally, which will cause the Aborted_clients variable to increase.

2. Got an error reading communication packets Cause Analysis

Which situation will cause an alarm similar to "Aborted connection xxxx to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)" to appear in the error log? Next, we will do some specific tests based on the possible reasons above. Pay attention to the changes in the status variables Aborted_clients and Aborted_connects and the error log records for each test.

• Test 1: Wrong password, wrong user

1. Check the status variable value before testingmysql> show global status like 'abort%';+------------------+-------+| Variable_name | Value |+------------------+-------+| Aborted_clients | 0 || Aborted_connects | 0 |+------------------+-------+
2. Test process# mysql -uroot -pwrongpassmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)# mysql -uroot1 -pwrongpassmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'root1'@'localhost' (using password: YES)
3. View status changes and error logmysql> show global status like 'abort%';+------------------+-------+| Variable_name | Value |+------------------+-------+| Aborted_clients | 0 || Aborted_connects | 2 |+------------------+-------+Error log record:2020-03-16T17:58:35.318819+08:00 6 [Note] Access denied for user 'root'@'localhost' (using password: YES)2020-03-16T17:59:04.153753+08:00 7 [Note] Access denied for user 'root1'@'localhost' (using password: YES)
Result: Aborted_connects has an error log but no Aborted connection related records

• Test 2: Sleep timeout or manual session termination

1. Check the status variable value before testingmysql> show global status like 'abort%';+------------------+-------+| Variable_name | Value |+------------------+-------+| Aborted_clients | 0 || Aborted_connects | 2 |+------------------+-------+
Query OK, 0 rows affected (0.00 sec)
3. Check status changes and error logsmysql> show global status like 'abort%';+------------------+-------+| Variable_name | Value |+------------------+-------+| Aborted_clients | 1 || Aborted_connects | 2 |+------------------+-------+
Result: Aborted_clients is added but there is no record in the error log. Similarly, after the sleep time expires, Aborted_clients is added but there is a record of Aborted connection in the error log.

Abnormal session exit usually causes Aborted connection alarm. That is, we can reflect whether there is an abnormal session through the change of Aborted_clients status variable. Then the reason for the "Got an error reading communication packets" and similar alarms is very clear. After consulting relevant information, the possible reasons for Aborted connection alarm are summarized as follows:

1. The session connection was not closed properly and the program did not call mysql_close().
2. Sleep for a number of seconds longer than the wait_timeout or interactive_timeout parameters.
3. The query data packet size exceeds the max_allowed_packet value, causing the link to be interrupted.
4. Other network or hardware issues.
   

3. Problem avoidance and summary

In fact, Aborted connection alarms are difficult to avoid. There will be a small amount of Aborted connection information in the error log, which can be ignored. However, if Aborted connection alarms appear frequently in your error log, you should pay attention to it, as it may have a greater impact on your business. Here are some suggestions on how to avoid mistakes. I hope they will be helpful to you.

1. It is recommended that after the business operation is completed, the application logic will correctly close the connection and replace the long connection with a short connection.
2. Check to make sure the value of max_allowed_packet is high enough and that the client is not receiving a "packet too big" message.
3. Make sure the client application does not abort the connection. For example, if PHP has max_execution_time set to 5 seconds, increasing connect_timeout will not help because PHP will kill the script. Other programming languages ​​and environments have similar security options.
4. Make sure transactions (begin and commit) are done correctly to ensure that connections are left in a clean state once the application is finished.
5. Checks if skip-name-resolve is enabled, which checks if hosts are authenticated based on their IP addresses instead of their hostnames.
6. Try increasing MySQL's net_read_timeout and net_write_timeout values ​​to see if that reduces the number of errors.

The above is the detailed analysis of MySQL Aborted connection alarm log. For more information about MySQL Aborted connection alarm log, please pay attention to other related articles on 123WORDPRESS.COM!