Analyzing the troublesome Aborted warning in MySQL through case studies

This article mainly introduces the relevant content about Aborted alarm in MySQL, and shares it for your reference and learning. Let's take a look at the detailed introduction:

Actual Combat

Part 1: Written at the beginning

In the MySQL error log, we often see various types of Aborted connection errors. This article will conduct a preliminary analysis of such errors and understand the basic troubleshooting ideas and methods after a problem occurs. It is crucial to master this method, rather than guessing and trying when problems arise. When a problem occurs in the database, the DBA needs to solve the problem quickly in a short period of time. Therefore, the difference between a good and a bad DBA lies in this.

Part 2: Types

[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

Part 3: Key Parameter Analysis

wait_timeout

This parameter specifies the number of seconds the server waits for activity on a noninteractive connection before shutting it down.

interactive_timeout

This parameter specifies the number of seconds the server waits for activity before closing an interactive connection.

Warning: It is recommended to adjust these two parameters together to avoid some pitfalls.

The two parameter values ​​in this article use the default values

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 under what circumstances Aborted_clients will increase and under what circumstances Aborted_connects will increase

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

Part 4: Case 1

Here I deliberately entered the wrong password 5 times to see which parameter of the database error log and Aborted records this 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)

It can be seen that the Aborted_connects here records the problem of incorrect password

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 this type of password input error information

[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)

Part 5: Case 2

Next, let's look at the impact of the two key parameters mentioned in Section 3 on the behavior of database connections.

Here we configure both parameters to 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)

Here are three operations. You can see that the number of clients increases. This is because the timeout parameter controls the connection that has already connected to the data and is 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)

The error log records

[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)

Part 6: Case 3

In this case, we look at the impact 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 I see a problem with too many connections.

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

There is no record in the error log

Part 7: Case 4

When the third-party tool navicat select result is not out, choose stop and it will appear

clients rise

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)

Part 8: Summary of reasons

1. Connections sleeping for hundreds of seconds and often repeatedly in MySQL are one of the symptoms of an application not closing connections after work, but relying on the database wait_timeout to close them. It is strongly recommended to change the application logic to properly close the connection at the end of the operation;
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. In this case he will abort the connection instead of closing it properly;
3. Another possibility is TIME_WAIT. It is recommended that you make sure that the connection is properly managed and closed properly on the application side;
4. Make sure the transaction is committed correctly (started and committed) so that once the application is "done" with the connection it will be in a "clean" state;
5. You should ensure that the client application does not abort the connection. For example, if PHP's option max_execution_time is set to 5 seconds, increasing connect_timeout is useless because PHP will kill the script. Other programming languages ​​and environments have similar options;
6. Another reason for connection delays is DNS issues. Check if skip-name-resolve is enabled, which checks that hosts are authenticated based on their IP addresses instead of their hostnames;
7. Try increasing MySQL's net_read_timeout and net_write_timeout values ​​to see if that reduces the number of errors.

Summarize

Through these four cases, we can understand the difference between Aborted_clients and Aborted_connects, and what kind of error log will be popped up under what circumstances. The several Aborted errors in the second section of the article are common errors. When such errors occur, you should have theoretical knowledge in mind to know what kind of errors will occur under what circumstances, so as to quickly locate the problem. Due to the author's limited level and the rush to write the article, there are inevitably some errors or inaccuracies in the article. I sincerely ask readers to criticize and correct any inappropriateness.

Well, the above is the full content of this article. I hope that the content of this article can bring some help to your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM.