MySQL server has gone away problem solving method

The problem with MySQL Error: (2006, ' MySQL server has gone away ') means that the link between the client and MySQL server is broken.

The reason for this is generally that the SQL operation takes too long, or that the data being transferred is too large (for example, using INSERT ...) the statement of values is too long, which can be avoided by modifying the configuration parameters of the max_allowed_packed, or by inserting the data in batches in the program.

There are many reasons for this problem, which is summarized under the online analysis:

Reason one. MySQL service is down.

A simple way to determine if this is the cause is to go to the MySQL console and see how long MySQL is running

Mysql> show global status like ' uptime ';
+---------------+---------+
| variable_name | Value |
+---------------+---------+
| Uptime | 3414707 |
+---------------+---------+

1 row in set or check MySQL error log to see if there is any information to restart

If the uptime value is large, it indicates that the MySQL service has been running for a long time. Indicates that the service has not been restarted recently.
If the log does not have relevant information, also the table name MySQL service has not been restarted recently, you can continue to check the following several things.

Cause two. MySQL Connection timed out

That is, a MySQL long connection has not been initiated for a long time, reached the server side timeout, was forcibly shut down by the server.
After this connection initiated the query, it will error server has gone away
(Most PHP scripts fall into this category)

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 |
+----------------------------+----------+
Ten rows in Set

Wait_timeout is 28,800 seconds, that is, the MySQL link is automatically closed after 28,800 seconds of no operation

Cause three. MySQL Request link process is active kill

This is similar to the reason two, just one person is a MySQL own action

Mysql> show global status like ' Com_kill ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Com_kill | 21 |
+---------------+-------+
1 row in set reason four. Your SQL statement was too large.

This error can also occur when the result set of the query exceeds Max_allowed_packet. The positioning method is to play the relevant error statement.

Export to a file using select * into outfile to see if the file size exceeds max_allowed_packet, and if it is exceeded, you need to adjust the parameters or refine the statement.

Mysql> show global variables like ' max_allowed_packet ';
+--------------------+---------+
| variable_name | Value |
+--------------------+---------+
| Max_allowed_packet | 1048576 |
+--------------------+---------+
1 row in Set (0.00 sec)

Modify Parameters:

mysql> set global max_allowed_packet=1024*1024*16;
Mysql> show global variables like ' max_allowed_packet ';
+--------------------+----------+
| variable_name | Value |
+--------------------+----------+
| Max_allowed_packet | 16777216 |
+--------------------+----------+
1 row in Set (0.00 sec)

Here are the additions:

The application executes batch MySQL statements for a long time. Executes a SQL, but the SQL statement is too large or contains a BLOB or LONGBLOB field in the statement. For example, the processing of image data. can easily cause MySQL server has gone away.

Today, with a similar scenario, MySQL is just coldly saying: MySQL server has gone away.

Probably a glance, mainly because of the following reasons:
One possibility is that the SQL statement sent is too long to exceed the size of the max_allowed_packet, and if this is the case, you just need to modify the my.cnf to increase the value of Max_allowed_packet.

Another possibility is that due to some reasons, such as the use of Singleton in the program to obtain a database connection, although the database is connected, but in fact, the same connection is used, and the program in a two operation of the database is more than the time interval of wait_timeout (SHOW Status to see this setting), then there may be a problem. The simplest way is to change the wait_timeout, of course, you can also in the program occasionally mysql_ping (), so that MySQL know it is not a person in combat.

fix MySQL server has gone away

1. The application executes batch MySQL statements for a long time. The most common is the acquisition or conversion of old and new data. or idle database connections for a long time (that's what my project is)
Solution:
Add or modify the following two variables in the my.cnf file:

1 wait_timeout=288000022880000

A specific description of the two variables can be Google or read the official manual. If you cannot modify MY.CNF, you can set client_interactive when you connect to the database, such as:

1 " Set interactive_timeout=24*3600 "  2

2. Execute a SQL, but the SQL statement is too large or the statement contains a BLOB or Longblob field. For example, the processing of image data
Solution:
Add or modify the following variables in the my.cnf file:

1

The Max_allowed_packet parameter is used to control the maximum length of its communication buffer.

Recently in a project, need the program 24 hours open, and there will be a lot of idle time, so every morning when the first operation of the database, there is "MySQL server has gone away" error message, The reason for this problem is that the database connection is automatically shut down because it has not been manipulated for a long time. To solve this problem, my experience has the following two points, may be useful for everyone:
The first method:
Of course is to increase your wait-timeout value, this parameter is set in MY.CNF (under Windows step down is My.ini), my database load is slightly larger, so I set the value to 10, (the unit of this value is the second, This means that when a database connection does not have any action within 10 seconds, it will be forcibly closed, I am not using permanent link (mysql_pconnect), with Mysql_connect, About this wait-timeout effect you can see in the MySQL process list (show processlist), you can set this wait-timeout to larger, such as 300 seconds, hehe, generally speaking 300 seconds enough to use, In fact you can also not set, MySQL default is 8 hours. The situation is determined by your server and site.
The second method:
This is also my personal view of the best method, that is, check the status of MySQL link, so that it relink. (with mysql_ping ())
It may be known that there is a mysql_ping such a function, in a lot of information that the Mysql_ping API will check whether the database is linked, if it is disconnected will try to reconnect, but in my test process found that the fact is not so, is conditional, must be passed Mysql_options this C API pass related parameters, let MySQL have the option to disconnect auto-link (mysql default is not automatically connected), but I test found in PHP's MySQL API does not take this function, you re-edit MySQL bar, hehe. But mysql_ping this function is finally able to use, but in which there is a small operation skills:

1 //use mysql_ping to check for re-connection. Use two functions, one is mysql_ping, the other is mysql_options. The specific use is to use mysql_options before Mysql_real_connect, after Mysql_init. Use the following:2  CharValue =1;3(void) Mysql_init (&MySQL);4Mysql_options (&mysql, Mysql_opt_reconnect, (Char*) &value);//set up automatic connections5 //then, before mysql_query, first use mysql_ping to determine if the connection has been disconnected, will automatically reconnect. 6Mysql_ping (&MySQL);7 //Ping () This function first detects whether the data connection is normal, if it is closed, the entire MySQL instance of the current script is closed, and then reconnected. 8 //after this processing, it is very effective to solve the problem of MySQL server has gone away, and it will not cause additional overhead to the system. 9 //mysql_ping will change the return value of the mysql_affected_rows. So it's best to add a mutex to the MySQL handle (preferably a read-write lock). When the other thread prepares to execute query, it acquires the lock and releases it after execution. The thread executing the mysql_ping attempts to acquire the lock before pinging, and if the fetch fails,Ten //then continue to sleep and give up this round of ping. 

Appendix:

Mysqli_options ()

Definitions and syntax

The Mysqli_options () function Sets additional connection options that affect the connection behavior.

The Mysqli_options () function can be called several times to set a number of options.

Note:the mysqli_options () function can be called after Mysqli_init () and before Mysqli_real_connect ().

Grammar:

1 mysqli_options (connection,option,value);

Connection: Required. Specifies the MySQL connection to use.

Option: Required. Specifies the options to set. Can be one of the following values:

• Mysqli_opt_connect_timeout-The connection time-out in seconds
• Mysqli_opt_local_infile-Enable/disable LOAD LOCAL INFILE
• Mysqli_init_command-execute commands after connecting to the MySQL server
• Mysqli_read_default_file-read options from a named file instead of MY.CNF
• Mysqli_read_default_group-read option from a named group in a file specified in my.cnf or mysqli_read_default_file
• Mysqli_server_public_key-RSA Public key file based on SHA-256 authentication

Value: Required. Specifies the value of option .

Mysql_ping ()

Mysql_ping: This function can be used for long-idle scripts to check if the server is shutting down the connection.

Definitions and syntax

NT StdCall mysql_ping (MySQL *mysql); Description: Check if the connection to the server is normal. If the automatic Reconnect feature is not disabled when the connection is disconnected, try reconnecting the server. This function can be used by the client to detect whether the connection to the server is closed after a long time of inactivity, and then reconnect if necessary. return value:
The connection is normal, 0 is returned, and a non-0 value is returned if an error occurs. Returning a value other than 0 does not mean that the server itself is shut down or the network may be causing network failure. Error code:
Cr_commands_out_of_sync command to perform Cr_server_gone_error server disconnection in an incorrect order

。。。

Grammar

Mysql_ping (Connection)

Connection: Optional. Specify MySQL connection. If not specified, the previous connection is used.

MySQL server has gone away problem solving method