MySQL-Aborted conn
转自：
MySQL Error Log Has Messages About "Aborted connection ... Communication Packets" (Doc ID 2322952.1)
1. 连接超时的各种原由
1.1 timeouts and errors
There are two classes for the aborted connection messages: timeouts and errors:
1) Got timeout: These messages are usually caused by the value set server-side for interactive_timeout, net_read_timeout, net_write_timeout, wait_timeout.
2) Got an error: These messages are usually caused by abnormal terminated connections or malformed network packets.
Some of the common reasons for these messages are:
Inactivity connection is timing out after being idle for wait_timeout or interactive_timeout seconds (which depends on whether it is an interactive connection or not).
Connection is terminated abnormally, for example because the application crashes or do not close the connection explicitly.
Network delay or outage.
The row has more data than max_allowed_packet of client. See also Packet Too Large in the Reference Manual.
Note: The messages may start to appear after changing log_warnings to 2 in MySQL 5.6 or earlier or log_error_verbosity to 3 in MySQL 5.7 or later as the messages only are logged at these levels. This does not mean the condition triggering the message did not occur before changing the log level. MySQL 5.7 defaults to log_error_verbosity = 3 which is also the recommended value in 5.7. In MySQL 8.0 the default is reduced to 2 as some important messages that does not report issues have been changed to system level messages that are always logged.
1.2 案例
The four messages, what they mean, and examples of triggering them will be discussed in the remainder of the section.
1）Got Timeout Reading Communication Packets
This message mainly happens when the connection is timing out due to inactivity (Sleep status) for longer than wait_timeout or interactive_timeout seconds. The wait_timeout setting is used when the client/application is considered non-interactive; the interactive_timeout setting is used for clients that are considered interactive, for example using the mysql command-line client in interactive mode.
The message also happens when Server has not got any message from client for more than net_read_timeout seconds for some reason.
An example of triggering the message due to inactivity is:
session 1> select @@global.wait_timeout, @@global.interactive_timeout; +-----------------------+------------------------------+ | @@global.wait_timeout | @@global.interactive_timeout | +-----------------------+------------------------------+ | 28800 | 28800 | +-----------------------+------------------------------+ 1 row in set (0.00 sec) session 1> set global interactive_timeout=10; Query OK, 0 rows affected (0.00 sec) session 1>select @@global.wait_timeout, @@global.interactive_timeout; +-----------------------+------------------------------+ | @@global.wait_timeout | @@global.interactive_timeout | +-----------------------+------------------------------+ | 28800 | 10 | +-----------------------+------------------------------+ 1 row in set (0.00 sec) ^^^ Global value of interactive_timeout has been changed to 10 seconds which means new connection will be applied changed value. shell$ mysql -utest_user --host=192.0.0.1 --prompt='session 2>' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 Server version: 5.7.20-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. session 2> ^^^ New connection was established. session 1> show processlist; +----+-----------+-------------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------+-------------------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | | 7 | test_user | 192.0.2.101:43822 | NULL | Sleep | 6 | | NULL | +----+-----------+-------------------+------+---------+------+----------+------------------+ 2 rows in set (0.00 sec) session 1> show processlist; +----+-----------+-------------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------+-------------------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | | 7 | test_user | 192.0.2.101:43822 | NULL | Sleep | 10 | | NULL | +----+-----------+-------------------+------+---------+------+----------+------------------+ 2 rows in set (0.00 sec) ^^^ Connection id 7 was being idle for 10 seconds session 1> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec) ^^^ After 10 seconds of idling, the id 7 was terminated by mysqld
2017-12-08T16:26:10.932954+10:00 7 [Note] Aborted connection 7 to db: 'unconnected' user: 'test_user' host: '192.0.2.101' (Got timeout reading communication packets)
2）Got an Error Reading Communication Packets
This message mainly happens when the connection is terminated abnormally. Examples of what is considered an abnormal termination of the connection are:
•The application crashes.
•The application shuts down cleanly but does not close the connection.
•The connection is closed by the firewall or due to the keepalive settings.
The message can easily be re-produced by kill -9 command:
session 1> SHOW PROCESSLIST; +----+-----------+-------------------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------+-------------------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST | | 9 | test_user | 192.0.2.101:43824 | NULL | Sleep | 5 | | NULL | +----+-----------+-------------------+------+---------+------+----------+------------------+ 2 rows in set (0.00 sec) ^^^ Connection id 9 was establish from 192.0.2.101. [root@testvm2 ~]# ps -ef | grep test_user root 4140 3987 3 00:57 pts/0 00:00:00 /db/5.7/bin/mysql -utest_user --host=192.0.2.1 --prompt=session 2> root 4142 4067 0 00:57 pts/1 00:00:00 grep test_user [root@testvm2 ~]# kill -9 4140 ^^^ Kill the connection process forcely
Then aborted messages was written at MySQL error log (provided the error logging level is set high enough):
2017-12-08T16:31:20.319182+10:00 9 [Note] Aborted connection 9 to db: 'unconnected' user: 'test_user' host: '192.0.2.101' (Got an error reading communication packets)
mysql> CREATE TABLE test.test_tbl (a int unsigned NOT NULL PRIMARY KEY, b varchar(16385)); Query OK, 0 rows affected (0.07 sec) mysql> INSERT INTO test.test_tbl VALUES (1, REPEAT('a', 16300)); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO test.test_tbl VALUES (2, REPEAT('a', 16300)); Query OK, 1 row affected (0.02 sec) mysql> INSERT INTO test.test_tbl VALUES (3, REPEAT('a', 16385)); Query OK, 1 row affected (0.02 sec) mysql> SELECT a, LENGTH(b) FROM test.test_tbl; +------+-----------+ | a | LENGTH(b) | +------+-----------+ | 1 | 16300 | | 2 | 16300 | | 3 | 16385 | +------+-----------+ 3 rows in set (0.00 sec) ^^^ Insert 3 rows which have around 16300, 16300 , 16385 bytes. shell$ mysql --max-allowed-packet=16384 -utest_user --host=192.0.2.1 -e "SELECT b FROM test.test_tbl WHERE a = 1" > x mysql: [Warning] Using a password on the command line interface can be insecure. ^^^ First row which has around 16300 bytes was exported without errors since max_allowed_packet (16384 bytes) was greater than a row. shell$ mysql --max-allowed-packet=16384 -utest_user --host=192.0.2.1-e "SELECT b FROM test.test_tbl WHERE a IN (1,2)" > x ^^^ Each row which has around 16300 bytes was executed without errors since max_allowed_packet (16384 bytes) was greater than any rows. shell$ mysql --max-allowed-packet=16384 -utest_user --host=192.0.2.1-e "SELECT b FROM test.test_tbl WHERE a = 3" > x ERROR 2020 (HY000) at line 1: Got packet bigger than 'max_allowed_packet' bytes ^^^ The error happened because exported row was greater than max_allowed_packet.
Then aborted messages was written at MySQL error log as below
2017-12-09T06:56:25.040447+10:00 171 [Note] Aborted connection 171 to db: 'test' user: 'test_user' host: '192.0.2.101' (Got an error reading communication packets)3）Got Timeout Writing Communication Packets
This message happens when a network packet has not reached the client for more than net_write_timeout seconds.
It also happens when exporting large rows (for example blob/text data) by mysqldump: MySQL Server is trying to send the data, but mysqldump does not write as fast MySQL Server sends data, thus MySQL Server can end up waiting for net_write_timeout and close the connection.
An example is:
session 2> connect Connection id: 165 Current database: *** NONE *** session 2> SELECT * FROM test.tbl; ^^^ Connection id 165 was establish then requesting the large result set session 1> SHOW FULL PROCESSLIST; +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ | 165 | test_user | 192.0.2.101:44022 | NULL | Query | 1 | Sending to client | SELECT * FROM test.tbl | | 166 | root | localhost | test | Query | 0 | starting | SHOW FULL PROCESSLIST | +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ 2 rows in set (0.00 sec) # Assuming eth3 is used for the connection from 192.0.2.101 to MySQL Server: shell$ sudo ifdown eth3 ^^^ Shut down network eth3 interface which is used by session 2. session 1> SHOW FULL PROCESSLIST; +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ | 165 | test_user | 192.0.2.101:44022 | NULL | Query | 60 | Sending to client | SELECT * FROM test.tbl | | 166 | root | localhost | test | Query | 0 | starting | SHOW FULL PROCESSLIST | +-----+-----------+-------------------+------+---------+------+-------------------+------------------------+ 2 rows in set (0.00 sec) session 1> SHOW FULL PROCESSLIST; +-----+------+-----------+------+---------+------+----------+-----------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+------+---------+------+----------+-----------------------+ | 166 | root | localhost | test | Query | 0 | starting | SHOW FULL PROCESSLIST | +-----+------+-----------+------+---------+------+----------+-----------------------+ 1 row in set (0.00 sec) ^^^ After 60 seconds of waiting, connection id 165 was terminated by MySQL Server.
Then aborted messages was written at MySQL error log (provided the error logging level is set high enough):
2017-12-09T06:59:22.654276+10:00 165 [Note] Aborted connection 165 to db: 'unconnected' user: 'test_user' host: '192.0.2.101' (Got timeout writing communication packets)
4）Got an Error Writing Communication Packets
This message happens when a network packet has not reached the client due to abnormal terminated connections or malformed network packets.
The causes of this message are similar as the ones of "Got an Error Reading Communication Packets".
It mainly happens when the connection is terminated abnormally. Examples of what is considered an abnormal termination of the connection are:
•The application crashes.
•The application shuts down cleanly but does not close the connection.
•The connection is closed by the firewall or due to the keepalive settings.
The message can easily be re-produced by kill -9 command:
mysql> SHOW FULL PROCESSLIST; +--------+------+-------------------+------+---------+------+-------------------+---------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+------+-------------------+------+---------+------+-------------------+---------------------------------+ | 844752 | root | localhost | test | Query | 0 | starting | SHOW FULL PROCESSLIST | | 844760 | root | 192.0.2.101:46924 | NULL | Query | 8 | Sending to client | select * from test.tt1,test.tt2 | +--------+------+-------------------+------+---------+------+-------------------+---------------------------------+ 2 rows in set (0.00 sec) ^^^ Connection id 844760 was establish from 192.0.2.101. shell$ ps -ef | grep mysql root 10806 9624 60 00:06 pts/0 00:00:03 mysql -uroot -px xx -h 192.0.2.100 -e select * from test.tt1,test.tt2 root 10816 9964 0 00:06 pts/1 00:00:00 grep mysql shell$ kill -9 10806 ^^^ Kill the connection process forcefully
Then aborted messages was written at MySQL error log:
2019-06-23T15:38:47.042130Z 844760 [Note] Aborted connection 844760 to db: 'unconnected' user: 'root' host: '192.0.2.101' (Got an error writing communication packets)The message also happens when exceeding the max_allowed_packet setting or a malformed network packet is transferred to a client by some reason:
mysql> CREATE TABLE test.tt3 (c1 longtext); Query OK, 0 rows affected (0.21 sec) mysql> INSERT test.tt3 VALUE (REPEAT('abcd',1000000)); Query OK, 1 row affected (1.45 sec) mysql> SELECT LENGTH(c1) FROM test.tt3; +------------+ | LENGTH(c1) | +------------+ | 4000000 | +------------+ 1 row in set (0.01 sec) ^^^ Insert 1 row which have around 4000000 bytes. shell$ mysql -uroot -proot -h 192.0.2.100 --max-allowed-packet=1234567 mysql> USE test Database changed mysql> SELECT * FROM tt3; ERROR 2020 (HY000): Got packet bigger than 'max_allowed_packet' bytes ^^^ The error happened because mysql client got a row which was greater than its max_allowed_packet.
Then the aborted message was written at MySQL error log as below:
2019-06-24T00:41:35.041189Z 844763 [Note] Aborted connection 844763 to db: 'test' user: 'root' host: '192.0.2.101' (Got an error writing communication packets)2. 解决措施
In general you need to check the application and network for errors or overload issues. It is also recommended to read Communication Errors and Aborted Connections in the Reference Manual. The rest of the section will discuss some steps and ideas to identify and implement a solution specifically for the three messages.
2.1 Got Timeout Reading Communication Packets
The following considerations should be made to determine why timeout messages occur and how to avoid them:
•Is the value of interactive_timeout and/or wait_timeout too small? The default values are 8 hours (28800 seconds), but if you have changed the value it may be too short for your workload. In that case, increase one or both of the values. •If the timeout values are large enough, determine why the connections are unused for extended periods of time. Examples are:◦If you are using a connection pool, the pool may be too large and you can consider reducing the size or have the pool close idle connections. ◦A long running application may create new connections, then not use them again. In that case make sure the application closes the connection or use a connection pool. •Check the network for problems. If the network in general cannot keep up, it may be necessary to increase net_read_timeout to avoid timeouts.
2.2 Got an Error Reading Communication Packets
To solve the issues causing the got an error reading communication packets check the following things:
•Check the application logs. Are there any signs of the application restarting unexpectedly? If so, investigate why this happens. •Verify that the application explicitly closes all database connections before shutting down. If not, add code to close the connections. The exact method to do this depends on the connector/API you are using. •Check the network and network services such as firewalls whether they can cause messages not to arrive. •If you are working with large rows (in number of bytes) consider increasing max_allowed_packet both server and client-side or change the query to work with less data per row (for example explicitly list the columns to retrieve in a SELECT statement rather than using SELECT *). •Whether using a connector, check sockets timeouts are sufficiently high (e.g. socketTimeout for Connector/J is explicitly recommended to be disabled or increased)
2.3 Got Timeout Writing Communication Packets
Check the following points to solve the issues with timeouts writing packets:
•Check the application logs to determine what it was doing. If it was busy doing some task for a long time while received data (for example mysqldump being busy writing out the data to disk), investigate what can be done to break up the task in smaller parts, so the application will not block. •Check the network for problems. •One option is to increase net_write_timeout to allow MySQL Server to wait for longer before timing out. However, fixing the underlying issue is preferred.
2.4 Got an Error Writing Communication Packets
Similar as the got an error reading communication packets message, check the following things to solve this issue:
•Check the application logs. Are there any signs of the application restarting unexpectedly? If so, investigate why this happens. •Verify that the application explicitly closes all database connections before shutting down. If not, add code to close the connections. The exact method to do this depends on the connector/API you are using. •Check the network and network services such as firewalls whether they can cause messages not to arrive. •If you are working with large rows (in number of bytes) consider increasing max_allowed_packet both server and client-side or change the query to work with less data per row (for example explicitly list the columns to retrieve in a SELECT statement rather than using SELECT *). •Whether using a connector, check sockets timeouts are sufficiently high (e.g. socketTimeout for Connector/J is explicitly recommended to be disabled or increased)
