MySQL server has gone away problem solving method

Original address: http://www.jb51.net/article/23781.htm

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:

Applications (such as PHP) Execute 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 (such as PHP) for a long time to execute the batch of MySQL statements. The most common is the acquisition or conversion of old and new data.
Solution:
Add or modify the following two variables in the my.cnf file:
wait_timeout=2880000
Interactive_timeout = 2880000
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:
sql = "Set interactive_timeout=24*3600";
Mysql_real_query (...)


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:
Max_allowed_packet = 10M (You can also set the size you want)
The Max_allowed_packet parameter is used to control the maximum length of its communication buffer.


Recently do the site has a station to use the Web page grabber function, when a PHP script in the request URL, the requested page may be very slow, more than the MySQL wait-timeout time, and then when the Web content is captured, ready to insert into MySQL, found that the MySQL connection timeout was turned off, so there is "MySQL server has gone away" error, 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.
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:
This is one of the functions in the middle of my database operations class

Copy CodeThe code is as follows:
function ping () {
if (!mysql_ping ($this->link)) {
Mysql_close ($this->link); Note: Be sure to perform a database shutdown first, which is the key
$this->connect ();
}
}

The code that I need to call this function might be like this

Copy CodeThe code is as follows:
$str = file_get_contents (' http://www.jb51.net ');
$db->ping ();//After the previous page crawl, or cause the database connection to close, check and reconnect
$db->query (' SELECT * from table ');

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.
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.
__________________________________________________________________________________________________
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 (such as PHP) for a long time to execute the batch of MySQL statements. The most common is the acquisition or conversion of old and new data.
Solution:
Add or modify the following two variables in the my.cnf file:
wait_timeout=2880000
Interactive_timeout = 2880000 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:
sql = "Set interactive_timeout=24*3600";
Mysql_real_query (...)
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:
Max_allowed_packet = 10M (You can also set the size you want)
The Max_allowed_packet parameter is used to control the maximum length of its communication buffer.
1, the application (such as PHP) for a long time to execute the batch of MySQL statements.
The most common is the acquisition or conversion of old and new data.

Solution:

Add or modify the following two variables in the My.ini file:
wait_timeout=2880000
Interactive_timeout = 2880000

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:
sql = "Set interactive_timeout=24*3600";
Mysql_real_query (...)

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 Solutions

Add or modify the following variables in the my.cnf file:
Max_allowed_packet = 10M (You can also set the size you want)

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

Modify method

1) Method 1
You can edit the my.cnf to modify (under Windows My.ini) and modify it in the [Mysqld] section or in the MySQL server configuration section.

Max_allowed_packet = 20M
If you can't find the MY.CNF, you can pass

MySQL--help | grep my.cnf
To find the my.cnf file.

2) Method 2
(very compromise, very tangled approach)

Go to MySQL server

Run in the MySQL command line

Set Global Max_allowed_packet = 2*1024*1024*10
Then shut down this MySQL server link and then enter.

Show VARIABLES like '%max_allowed_packet% ';
See if the next Max_allowed_packet is edited successfully

------------the following is the web search data -------------------

Maybe other people have this problem, not necessarily the reason here, then put me on the internet to find a more comprehensive analysis below:
There are two articles, the first is more intuitive, the second is more esoteric.
Fix MySQL server has gone away 2009-01-09 16:23:22

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 (such as PHP) for a long time to execute the batch of MySQL statements. The most common is the acquisition or conversion of old and new data.

Solution:

Add or modify the following two variables in the my.cnf file:

wait_timeout=2880000
Interactive_timeout = 2880000
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:

sql = "Set interactive_timeout=24*3600";
Mysql_real_query (...)

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:

Max_allowed_packet =10M
(You can also set the size you want)

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

MySQL: Spooky MySQL server has gone away and its solution

Performing a show status in MySQL usually pays more attention to the cache effect, the number of processes, and so on, often ignoring two values:

Variable_name Value
Aborted_clients 3792
Aborted_connects 376


It usually accounts for only the 0.0x% of query, so it is not taken seriously. And in the traditional Web application, the query error on the user's impact is not big, just re-refresh the page is OK. In the recent Foundation transformation, many applications run as service and cannot prompt the user to refresh, in which case the quality of the service may be affected.

Through the program script log trace, the main error message is "MySQL server has gone away". The official explanation is:

The most common reason for the MySQL server have gone away error is so the server timed out and closed the connection.

Some Other common reasons for the MySQL server have gone away error is:

You (or the DB administrator) have killed the running thread with a Kill statement or a mysqladmin kill command.

You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should is corrected.

A client application running on a different host does not having the necessary privileges to connect to the MySQL server fro M that host.

You got a timeout from the TCP/IP connection on the client side. This could happen if you have been using the commands:mysql_options (..., mysql_opt_read_timeout,...) or mysql_options (..., Mysql_opt_write_timeout,...). In this case increasing the timeout could help solve the problem.

You had encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect Flag in the MYSQL structure are equal to 0).

You are using a Windows client and the server had dropped the connection (probably because wait_timeout expired) before th e command was issued.

The problem on Windows are that in some cases MySQL doesn ' t get a error from the OS if writing to the TCP/IP connection To the server, but instead gets the error when trying to read the answer from the connection.

Even if the reconnect flag in the MySQL structure are equal to 1, MySQL does not automatically reconnect and Re-issue the query as it doesn ' t know if the server did get the original query or not.

The solution to the either does a mysql_ping on the connection if there have been a long time since the last query (th Is are what MyODBC does) or set wait_timeout on the MYSQLD servers so high that it's in practice never times out.

You can also get these errors if your send a query to the server which is incorrect or too large. If Mysqld receives a packet that's too large or out of order, it assumes that something have gone wrong with the client an D Closes the connection. If you need big queries (for example, if is working with big BLOB columns), you can increase the query limit by Setti ng the server ' s Max_allowed_packet variable, which has a default value of 1MB. Also need to increase the maximum packet size on the client end. More information on setting the packet size was given in section a.1.2.9, "Packet Too large".

An INSERT or REPLACE statement This inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; Thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

You also get a lost connection if you be sending a packet 16MB or larger if your client is older than 4.0.8 and your serv Er is 4.0.8 and above, or the other the around.

It is also possible to see this error if hostname lookups fail (for example, if the DNS server on which your server or net Work relies goes down). This is because MySQL are dependent on the host system for name resolution, but have no how to knowing whether it is working -from MySQL's point of view the problem are indistinguishable from any other network timeout.

Also see the MySQL server have gone away error if MySQL is started with the--skip-networking option.

Another networking issue that can cause this error occurs if the MySQL port (default 3306) was blocked by your firewall, th US preventing any connections for the MySQL server.

You can also encounter this error with applications that fork child processes, all of which try-to-use the same connection to the MySQL server. This can is avoided by using a separate connection for each child process.

You had encountered a bug where the server died while executing the query.



According to this analysis, there are 3 possible reasons:

The 1,mysql server does not match the client version.

2,mysql Service-side configuration is defective or under-optimized

3, need to improve the program script

By replacing multiple server and client versions, it was found that only some of the errors were reduced and could not be fully resolved. Exclude 1.

The service side has been thoroughly optimized and has not been able to achieve the desired results. The value setting for timeout, from 10 of experience value to PHP default of 60, has been tried several times. The official MySQL default (8 hours) is obviously not possible. Thus the 2 were also excluded. (More optimized experience sharing will be provided later in the pack)

For 3 of the program Code Analysis, found that the program has a large number of applications similar to the following code (for ease of understanding, with the original API description):

$conn =mysql_connect (...);

... ... ... ...

if (! $conn) {//reconnect

$conn =mysql_connect (...);

}

mysql_query ($sql, $conn);

The meaning of this code is consistent with MySQL's official recommended method of thinking [If you have a script, you just has to issue the query again for the client to do a automatic Reconnection. ]。 In the actual analysis, it was found that if (! $conn) is not reliable, the program passed the IF (! $conn) test, will still return the above error.

The program was rewritten:

if (!conn) {//Connect ...}

ElseIf (!mysql_ping ($conn)) {//Reconnect ...}

mysql_query ($sql, $conn);

After the actual observation, MySQL server has gone away error basic solution.

BTW: Comes with a question about reconnect,

In PHP4X+CLIENT3X+MYSQL4X's old environment, the Reconnet code:

$conn =mysql_connect (...) can work correctly.

However, in the new environment of PHP5X+CLIENT4X+MYSQL4X, $conn =mysql_connect (...) The returned $conn are not available in some cases. Need to be written as:

Mysql_close ($conn);

$conn =mysql_connect (...);

The returned $conn can only be used normally. The reasons are not clear. No further research has been done and no discussion has been made. Maybe it will be in the official MySQL bug report.

~ ~ Hehe ~


This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/brightsnow/archive/2009/03/17/3997705.aspx


Description
Remember that your MySQL "max_allowed_packet" configuration setting (default 1MB)
MySQL default maximum can handle is 1MB
This problem occurs if you use large text or BLOB data in SQL. Comments on the PHP manual

When trying to inserts or UPDATE and trying to put a large amount of the text or data (BLOB) into a MySQL table you might run I nto problems.
In Mysql.err-might see:
Packet too Large (73904)
To fix you just has to start the MySQL with the Option-o max_allowed_packet=maxsize
You would just replace maxsize with the max size you want to insert, the default is 65536


The MySQL manual says

Both the client and the server has their own max_allowed_packet variable, so if you want to handle big packets, you must Increase this variable both in the client and in the server.

If you were using the MySQL client program, it default Max_allowed_packet variable is 16MB. To set a larger value, start mysql like this:

shell> MySQL--max_allowed_packet=32m that sets the packet size to 32MB.

The server ' s default max_allowed_packet value is 1MB. Can increase this if the server needs to handle big queries (for example, if is working with big BLOB columns). For example, to set the variable to 16MB, start the server like this:

Shell> mysqld--max_allowed_packet=16m You can also use a option file to set Max_allowed_packet. For example, to set the size for the server to 16MB, add the following lines in an option file:

[mysqld]max_allowed_packet=16m

When using MySQL to do a database restore, because some data is very large, this error occurs: The MySQL server returned this error:mysql error nr.2006-mysql server have gone away. This error occurred when I restored a 150MB backup. The solution is to find the MySQL installation directory, locate the My.ini file, and add it at the end of the file: Max_allowed_packet = 10M (You can also set the size you want). The Max_allowed_packet parameter is used to control the maximum length of its communication buffer.

MySQL server has gone away problem solving method