Solution to the MySQL server has gone away error

MySQL server has gone away issue in PHP

1. Background

I have written a script similar to the console command line in Codeigniter before. The sleep statement in the script lasted for a long time, which resulted in a phenomenon that the SQL operations before the sleep were all successful, but after the sleep, the SQL operation was executed and an error message was reported: MySQL server has gone away. That is, the MySQL connection is invalid. Later, I found out that there are two important configuration parameters in MySQL:

interactive_timeout
wait_timeout

The units of these two parameters are seconds (s). The default value is 8 hours (28800). The word interactive_timeout refers to the interactive timeout. There are generally two types of mysql connection methods, one is called "interactive" and the other is called "non-interactive". The common connection using mysql -u root xxx or master-slave replication is an "interactive connection", and the connection method using Java's JDBC or PHP's PDO driver is generally a "non-interactive connection". However, if interactive_timeout is not modified, this value will not change, but wait_timeout has different values ​​under different connection methods.

The wait_timeout value is the interactive_timeout value in "interactive connection". If it is a "non-interactive connection", the wait_timeout value is the original value configured in mysql.cnf.

In the end, only the wait_timeout value works. This configuration item limits the connection in the sleep state (view the current number of connections through show processlist). If the sleep time of this connection exceeds the wait_timeout value, the connection will be disconnected or cleared.

2. Wait_timeout analysis

1. First check the mysql.conf configuration

First, we configure interactive_timeout=10 wait_timeout=5. Now, we can check the values ​​of these two configuration items through the MySQL client (interactive connection): show variables like '%timeout%';

2. Interactive Connection

The result on the client: wait_timeout is not 10s as configured in our msyql.conf, but 5s.

Then let's take a look at what structure we get when PHP connects to MySQL (non-interactive connection) and executes the same statement:

3. Non-interactive connection

At this point, wait_timeout is the value we originally configured in mysql.cnf.

To sum up: the wait_timeout value is different in different "connection modes".

3. Analysis of the reasons for gone away

Combining the above situation, we know. At the beginning, some SQL statements are executed successfully, but the subsequent SQL statements fail to execute and report the error "gone away". Most of the reasons are that the connection has been idle for more than the wait_timeout, and the MySQL server unilaterally disconnects the connection. However, the client code is still using this connection variable, thinking that the connection is still OK (in fact, the MySQL server has been disconnected, but we just think that the connection is still valid), and it will inevitably report an error when executing SQL.

So how do we solve this situation?

1. You can adjust the wait_timeout value appropriately and increase it so that it is not easy to trigger the gone away situation. However, the disadvantage is that the long connection in sleep is not cleaned up and resources are wasted.

2. If the gone way MSYQL connection problem is thrown through try-cach, first call close() on the previous db. Then re-acquire the db connection open, and then execute the previous code. However, the code looks very painful. Pseudo code:

$db = db();

try {
   fun1$db); // The execution is successful at the beginning sleep(3600*10) // Assume that the sleep time is 10 hours fun2($db); // After 10 hours, the connection is terminated by MySQL, resulting in an error gone away
}catch(Exception $e) {
  // After the error is reported, we close the invalid connection and open a new connection $db->close();
  $db->open();
  // Get a new connection and execute // fun2($db)  
}

3. If you are using a framework like swoole or easyswoole, it is recommended to use the mysql pool connection pool. And generally the connection pool has settings for heartbeat check ping, connection survival detection interval, maximum number of idle connections, etc., so you only need to configure it once. For example, you can configure the active connection interval to be shorter to ensure that the connection will not be killed by the msyql server. For example, the easyswoole configuration:

For example, I set wait_timeout=10 before, but if I didn't change the interval of easyswoole's mysql connection pool activity test to a smaller time, the same gone way situation would occur. The first access to the interface successfully returned the SQL execution result, but when I accessed the interface again after more than 10s, an error message "mysql has gone away" was reported. After modifying setIntervalCheckTime(), this problem will not occur. We use mysql's show processlist to check the number of connections:

These are the number of connections that EasySwoole helps us maintain. When the sleep exceeds 3 seconds, since the check time is 3 seconds to survive, the connection pool helps us keep alive, and the sleep time starts from 0 again.

The following are the additions from other netizens

Enter MySQL

cmd

mysql -u username -p password

When we use MySQL to import a large file sql, we may get an error message saying MySQL server has gone away. The problem is that the default value of max_allowed_packet is too small. You only need to increase the value of this item and then import it again to succeed. The function of this item is to limit the size of the package received by the MySQL server. Therefore, if the imported file is too large, it may exceed the value set in this item, resulting in unsuccessful import! Next, let's take a look at how to view and set the value of this item.

Check the value of max_allowed_packet

show global variables like 'max_allowed_packet';
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+

You can see that the default size of this item is only 4M. Next, set the value to 150M (1024*1024*150)

set global max_allowed_packet=157286400;

Check the size again

show global variables like 'max_allowed_packet';

By increasing this value, generally speaking, importing a large amount of data into SQL again should be successful. If an error still occurs, just increase it. Please note that the setting in the command line is only valid for the current time. After restarting the MySQL service, the default value will be restored. However, you can modify the configuration file (you can add max_allowed_packet=150M in the configuration file my.cnf) to achieve permanent validity. However, we don’t often import large amounts of data, so I personally think that the current configuration can be effective through the command line, and there is no need to modify the configuration file.

The above are all the relevant knowledge points introduced this time. I hope that the content compiled by 123WORDPRESS.COM can help you.