MySQL Analysis of Abrted Alert Log

actual combat

Part1: Write at the top

In the error log of MySQL, we often see some kinds of borted connection errors, this article will make a preliminary analysis of such errors, and understand the basic ideas and methods of investigation after a problem arises. It's important to master this method, not to have problems, guess, try. When database problems arise, DBA needs to solve them quickly in a short time, so the difference between a good DBA and a bad DBA lies in this.

Part2: Species

[Warning] Aborted connection 305628 to db: 'db' user: 'dbuser' host: 'hostname' (Got an error reading communication packets)
[Warning] Aborted connection 81 to db:'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication
packets)
[Warning] Aborted connection 109 to db:'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password: YES)
[Warning] Got an error writing communication packets

Part3: Analysis of Key Parameters

wait_timeout

This parameter refers to the number of seconds that the server waits for activity on a non-interactive connection before the database system closes it.

interactive_timeout

This parameter refers to the number of seconds the server waits for activity before closing the interactive connection.

Warning: Warning: The two parameters are suggested to be adjusted together to avoid some pits.

The default values are used for the two parameters in this paper.

mysql> show global variables like '%timeout%';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
|interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
|wait_timeout               | 28800    |
+----------------------------+----------+
10 rows in set (0.01 sec)

In addition, in the database, we focus on these two parameters to see when Aborted_clients will increase and under what circumstances Aborted_connections will increase.

mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name    | Value |
+------------------+-------+
|Aborted_clients  | 19    |
|Aborted_connects | 0     |
+------------------+-------+
2 rows inset (0.00 sec)

Part4: Case 1

Here I intentionally enter the wrong password five times to see which parameter of the database error log and Arborted records the problem.

[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)
[root@HE3~]# mysql -uroot -pwrongpass -h127.0.0.1
ERROR 1045 (28000): Access denied for user 'root'@'127.0.0.1' (using password: YES)

As you can see, Aborted_connections here records the problem of password errors

mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name    | Value |
+------------------+-------+
|Aborted_clients  | 19    |
|Aborted_connects | 5     |
+------------------+-------+
2 rows inset (0.00 sec)

The error log also records information about such password errors.

[Warning] Access denied for user'root'@'127.0.0.1' (using password: YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)
[Warning] Access denied for user 'root'@'127.0.0.1' (using password:YES)

Part5: Case 2

Next, let's look at the impact of two key parameters mentioned in the third section of this article on the behavior of database connections.

Here we configure both parameters to be 10 seconds

mysql>set global wait_timeout=10;
Query OK,0 rows affected (0.00 sec)
 
mysql>set global interactive_timeout=10;
Query OK,0 rows affected (0.00 sec)
mysql>show processlist;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id: 79 Current database: *** NONE ***
 
+----+------+-----------------+------+---------+------+-------+------------------+
| Id |User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 79 |root | 127.0.0.1:42016 | NULL | Query  |    0 | NULL  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

In three operations, you can see the number of clients rising, which is due to the time out parameter control, the connection that has been connected to the data has been killed.

mysql>show global status like 'aborted%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect... Connection id:    81 Current database: *** NONE ***
 
+------------------+-------+
|Variable_name    | Value |
+------------------+-------+
|Aborted_clients  | 22    |
|Aborted_connects | 5     |
+------------------+-------+
2 rows in set (0.01 sec)

What is recorded in the error log is

[Warning] Aborted connection 81 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)
[Warning] Aborted connection 78 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets) 
[Warning] Aborted connection 79 to db: 'unconnected' user: 'root' host: '127.0.0.1' (Got timeout reading communication packets)

Part6: Case 3

In this case, we look at the effect of the maximum number of connections on the behavior of database connections.

mysql>show global variables like 'max_conn%';
+--------------------+-------+
|Variable_name      | Value |
+--------------------+-------+
|max_connect_errors | 1000  |
|max_connections    | 1024  |
+--------------------+-------+
2 rows in set (0.00 sec)
 
 
mysql>set global max_connections=2;
Query OK,0 rows affected (0.00 sec)

Here we see the problem of too many connections. 

[root@HE3~]# mysql -uroot -pMANAGER -h127.0.0.1
ERROR 1040 (HY000): Too many connections

The error log does not have any records.

Part7: Case 4

When the result of the third-party tool navicat select does not come out, the selection stop appears.

clients Up

mysql>show global status like 'aborted%';
+------------------+-------+
|Variable_name    | Value |
+------------------+-------+
|Aborted_clients  | 28    |
|Aborted_connects | 10    |
+------------------+-------+
2 rows in set (0.00 sec)

error log logging

170626 16:26:56 [Warning] Aborted connection 109 to db: 'helei1' user: 'sys_admin' host: '192.168.1.1' (Got an error writing communication packets)

Part8: Summary of Reasons

1. In MySQL, sleep status for hundreds of seconds and frequent repetitive connections are one of the symptoms of applications that do not close connections after work, but rely on the database wait_timeout to close them. It is strongly recommended that the application logic be changed to close the connection correctly at the end of the operation.
   

2. Check to ensure that the max_allowed_package value is high enough and that the client does not receive a "packet size" message. In this case, he will terminate the connection and not close it correctly.

3. Another possibility is TIME_WAIT. It is recommended that you confirm that the connection is properly managed and shut down normally on the application side.

4. Ensure that transactions are submitted correctly (start and commit) so that once the application "completes" the connection, it will be in a "clean" state;

5. <br class="Apple-interchange-newline"><div id="inner-editor"></div>

   You should ensure that the client application does not terminate the connection. For example, if the PHP option max_execution_time is set to 5 seconds, adding connect_timeout is useless because PHP kills scripts. Other programming languages and environments have similar options;

6. Another reason for connection delay is DNS problems. Check whether skip-name-resolve is enabled, and check that the host authenticates according to its IP address rather than its host name.

7. Try adding MySQL's net_read_timeout and net_write_timeout values to see if you reduce the number of errors.

8. 
   

—— Summary——

Through these four cases, we can understand the difference between Aborted_clients and Aborted_connections, and what kind of error logs will pop out under what circumstances. Several Aborted errors in the second section of this article are common errors. When these errors occur, we should have a theoretical knowledge in mind, know what kind of errors will occur under what circumstances, in order to locate them quickly. Questions. Because the author's level is limited and the writing time is also very hasty, there will inevitably be some mistakes or inaccuracies in the article, inappropriate places beg the reader to criticize and correct.