A case study of Aborted alarm in MySQL

This article mainly introduces the related contents of Aborted alarm in MySQL, and shares them for your reference and study. Let's take a look at the detailed introduction:

Actual combat

Part1: Write at the top

In error log of MySQL, we often see one kind of Aborted connection errors. In this paper, we will make a preliminary analysis of this kind of errors, and understand the basic troubleshooting ideas and methods after a problem occurs. It is very important to master this method, not to guess and try when there is a problem. When there is a problem with the database, DBA needs to solve the problem quickly in a short time, so the difference between a good and bad DBA lies in this.

Part2: Type

[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: Key parameter analysis

wait_timeout

Command-Line Format	--wait-timeout=#
System Variable	Name	wait_timeout
	Variable Scope	Global, Session
	Dynamic Variable	Yes
Permitted Values (Windows)	Type	integer
	Default	28800
	Min Value	1
	Max Value	2147483
Permitted Values (Other)	Type	integer
	Default	28800
	Min Value	1
	Max Value	31536000

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

interactive_timeout

Command-Line Format	--interactive-timeout=#
System Variable	Name	interactive_timeout
	Variable Scope	Global, Session
	Dynamic Variable	Yes
Permitted Values	Type	integer
	Default	28800
	Min Value	1

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

Warning: Warning 1 adjustment is recommended for these two parameters, which can avoid 1 pit.

The two parameter values in this article are 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 when Aborted_clients will be promoted and when Aborted_connects will be promoted

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 deliberately entered the wrong password 5 times to see which parameter of error, log and Aborted in the database records this 1 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_connects here records this 1 problem of password error

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

In error log, the information of this kind of password input error is also recorded

[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 the two key parameters mentioned in Section 3 of the article 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)

After three operations here, we can see that the number of clients rises, which is due to the control of timeout parameters, and the connection that has been connected with data 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)

error log

[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, let's 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)

I see here that the problem of too many connections broke out

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)

0

And there is no record in the error log

Part7: Case 4

If the result of the third party tool navicat select does not come out, it will appear when you select Stop

clients rose

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)

1

error log Logging

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)

2

Part8: Cause summary

In MySQL, sleep state for hundreds of seconds and often repeated connections are one of the symptoms of applications not closing connections after work, but relying on the database wait_timeout to close them. It is strongly recommended to change the application logic at the end of the operation to properly close the connection; Check to ensure that the value of max_allowed_packet is high enough and that the client does not receive the "packet is too big" message. In this case, he will abort the connection and close it incorrectly; Another possibility is TIME_WAIT. It is recommended that you confirm that the connection is properly managed and closed normally on the application side; Ensure that transactions commit correctly (start and commit) so that once the application "completes" the connection, it will be in the state of "clean"; You should ensure that the client application does not abort the connection. For example, if the option max_execution_time for PHP is set to 5 seconds, adding connect_timeout is useless because PHP kills the script. Other programming languages and environments have similar options; Another cause of connection delay is DNS problem. Check that skip-name-resolve is enabled and that the host authenticates against its IP address rather than its hostname; Try increasing the net_read_timeout and net_write_timeout values of MySQL to see if the number of errors is reduced.

Summarize

Through these four cases, we can understand the difference between Aborted_clients and Aborted_connects, and what kind of error logs will appear under what circumstances. Several Aborted errors in Section 2 of the article are common errors. When such errors occur, we should have a theoretical knowledge in mind to know what kind of errors will occur under what circumstances, so as to quickly locate problems. Due to the limited level of the author, the writing time is also very hasty, the text will inevitably appear some mistakes or inaccurate places, inappropriate places please criticize and correct readers.