Shulou(Shulou.com)06/01 Report--

MySQL's Analysis of aborted alarm Log

Part1: write at the front

In the error log of MySQL, we will often see some kinds of Aborted connection errors. This article will make a preliminary analysis of these errors and understand the basic troubleshooting ideas and methods after a problem arises. It is important to master this method, not to guess and try when something goes wrong. When there is a problem in the database, DBA is needed to solve the problem quickly in a short time, so a good DBA is different from a bad one.

Part2: category

[Warning] Aborted connection 305628 to db:' db' user: 'dbuser' host:' hostname' (Got an error reading communicationpackets) [Warning] Aborted connection 81 to db:'unconnected' user: 'root' host:' 127.0.0.1'(Got timeout reading communicationpackets) [Warning] Aborted connection 305628 to db:'helei1' user: 'sys_admin' host:' 192.168.1.1'(Got an error writing communicationpackets) [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

Command-Line Format--wait-timeout=#System VariableNamewait_timeoutVariable ScopeGlobal, SessionDynamic VariableYesPermitted Values (Windows) TypeintegerDefault28800Min Value1Max Value2147483Permitted Values (Other) TypeintegerDefault28800Min Value1Max Value31536000

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 VariableNameinteractive_timeoutVariable ScopeGlobal, SessionDynamic VariableYesPermitted ValuesTypeintegerDefault28800Min Value1

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

Warning: warning that these two parameters should be adjusted together to avoid some pitfalls.

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, let's focus on these two parameters to see when Aborted_clients will be improved and when Aborted_connects will be improved.

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 enter the wrong password five times to see which parameter of the database's error log and Aborted records this problem.

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

As you can see, the Aborted_connects 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 wrong information of this kind of password is also recorded in error log.

[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 take a look at the influence of the two key parameters mentioned in the third section 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 awayNo connection. Trying to reconnect... Connection id: 79 Current database: * NONE * * +-- +-+ | Id | User | Host | db | Command | Time | State | Info | | +-+ | 79 | root | 127.0.0.1 Query 42016 | NULL | Query | 0 | NULL | show processlist | +-+-| -+ 1 row in set (0.00 sec)

After three operations here, you can see the increase in the number of clients, which is controlled by the timeout parameter, and the connection that has been connected to the data is killed.

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

What is recorded in 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 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 we can see that there is a problem of too many connections.

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

There is no record in the error log.

Part7: case 4

If you choose to stop when the result of the third-party tool navicat select does not come out, 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)

Part8: summary of reasons

In MySQL, the sleep state of hundreds of seconds and often repeated connections is one of the symptoms that the application does not close the connection after work, but relies on the database wait_timeout to close them. It is strongly recommended that you change the application logic at the end of the operation to close the connection correctly

Check to make sure that the value of max_allowed_packet is high enough and that the client does not receive a "packet too large" message. In this case, he will abort the connection and incorrectly close it.

Another possibility is TIME_WAIT. It is recommended that you make sure that the connection is properly managed and closed on the application side.

Ensure that the transaction commits (starts and commits correctly) so that once the application "completes" the connection, it will be in the "clean" state

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, it is useless to add connect_timeout because PHP kills the script. Other programming languages and environments have similar options

Another cause of connection delay is a DNS problem. Check if 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 has been reduced.

-- Summary.

Through these four cases, we can understand the difference between Aborted_clients and Aborted_connects, and what kind of error log will be exposed under what circumstances. Several Aborted errors in the second section of the article are common mistakes. When such errors occur, you should have a theoretical knowledge in mind and know what kind of errors will occur under what circumstances, in order to quickly locate the problem. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

Like readers can click like to follow, your praise and attention is the greatest encouragement and support for the author to continue to post!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.