MySQL server has solution for gone away problem _mysql

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

The reason for this is that the SQL operation is typically too long, or that the data being transmitted is too large (for example, with inserts ...), which can be avoided by modifying the configuration parameters of the max_allowed_packed, or by inserting the data in the program in batches.

There are many reasons for this problem, summarizing the analysis on the Internet:

Reason one. MySQL service is down.

The way to determine if this is the case is simple, go to the MySQL console and see how long the MySQL runs

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

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

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

Reason two. MySQL Connection timeout

That is, a MySQL long connection for a long time did not launch a new request, reached the server side of the timeout, was forcibly shut down by the server.
After this connection to initiate the query, it will error the server has gone away
(Most PHP scripts are part of 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 without operation.

Reason three. MySQL Request link process is active kill

This situation is similar to the reason two, just one is the person who is the 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 also occurs when the result set of the query exceeds Max_allowed_packet. The location method is the statement that the relevant error is typed.

Export to a file with the select * into outfile to see if the file size exceeds max_allowed_packet, if it exceeds, you need to adjust the parameters, or optimize the statement.

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

To modify a parameter:

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)

The following are supplementary:

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 encountered a similar scenario, MySQL is just coldly said: MySQL server has gone away.

I have probably browsed for a few reasons, mainly because of the following:
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 reason, you just need to modify the MY.CNF to enlarge the Max_allowed_packet value.

There is also a possibility for some reason to timeout, such as the use of singleton in the process of getting a database connection, although the database is connected multiple times, but in fact, the same connection is used, and a two times in the program of the operation of the database Time exceeded wait_timeout (show Status to see this setting), 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 knows it is not a person in the fight.

troubleshoot MySQL server has gone away

1, applications (such as PHP) for a long time to execute the batch of MySQL statements. The most common is acquisition or new and old data conversion.
Solution:
Add or modify the following two variables in the my.cnf file:
wait_timeout=2880000
Interactive_timeout = 2880000
A specific description of two variables can be Google or read the Official Handbook. If you cannot modify MY.CNF, you can set up client_interactive when you connect to the database, for example:
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 function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer.


Recently do a site has a station to use the Web page collector function, when a PHP script in the request URL, it may be the requested page very slowly, 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 is closed, so there is a "MySQL server has gone away" such a false hint, to solve this problem, my experience has the following two points, may be useful to everyone:
The first method:
Of course increase your wait-timeout value, this parameter is set in MY.CNF (My.ini under Windows), my database load is slightly larger, so I set the value to 10, (this value is the unit of seconds, It means that when a database connection does not operate in 10 seconds, it will be forced to shut down, I am not using a permanent link (mysql_pconnect), with a mysql_connect, On 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, oh, 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 what I personally think is the best way to check the MySQL link state to make it relink.
Perhaps everyone knows that there is a mysql_ping such a function, in a lot of information that this mysql_ping API will check whether the database link, if it is disconnected will try to reconnect, but in my test process found that the fact is not so, is conditional, must pass Mysql_options this C API to pass the relevant parameters, so that MySQL has the option to disconnect automatic link (mysql default is not automatic connection), but I test found in the PHP MySQL API does not take this function, you re-edit MySQL bar, hehe. But mysql_ping this function is finally able to use, but to have a small operating skills:
This is a function in the middle of my database operations class.

Copy Code code as follows:

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

I need to call this function the code might be like this

Copy Code code as follows:

$str = file_get_contents (' http://www.jb51.net ');
$db->ping ()//After the previous web 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 shut down, and then reconnect.
After this processing, it is very effective to solve the problem of MySQL server has gone away, and will not incur additional overhead on the system.
__________________________________________________________________________________________________
Today encountered a similar scenario, MySQL is just coldly said: MySQL server has gone away.
I have probably browsed for a few reasons, mainly because of the following:
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 reason, you just need to modify the MY.CNF to enlarge the Max_allowed_packet value.
There is also a possibility for some reason to timeout, such as the use of singleton in the process of getting a database connection, although the database is connected multiple times, but in fact, the same connection is used, and a two times in the program of the operation of the database Time exceeded wait_timeout (show Status to see this setting), 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 knows it is not a person in the fight.
Troubleshoot MySQL server has gone away
1, applications (such as PHP) for a long time to execute the batch of MySQL statements. The most common is acquisition or new and old data conversion.
Solution:
Add or modify the following two variables in the my.cnf file:
wait_timeout=2880000
Interactive_timeout = 2880000 Specific descriptions of two variables can be Google or read the Official Handbook. If you cannot modify MY.CNF, you can set up client_interactive when you connect to the database, for example:
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 function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer.
1, applications (such as PHP) for a long time to execute the batch of MySQL statements.
The most common is acquisition or new and old data conversion.

Solution:

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

A specific description of two variables can be Google or read the Official Handbook.
If you cannot modify MY.CNF, you can set up client_interactive when you connect to the database, for example:
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 function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer.

Modify method

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

Max_allowed_packet = 20M
If you can't find my.cnf, you can pass the

MySQL--help | grep my.cnf
To find my.cnf files.

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

Access to MySQL server

Running on the MySQL command line

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

Show VARIABLES like '%max_allowed_packet% ';
See if Max_allowed_packet edits successfully

------------ The following is a network search information -------------------

Maybe other people come across this problem, not necessarily the reason for this, then, I find a more comprehensive analysis on the internet below:
There are two articles, the first one is more intuitive, the second is more esoteric.
Resolve MySQL server has gone away 2009-01-09 16:23:22

Today encountered a similar scenario, MySQL is just coldly said: MySQL server has gone away.
I have probably browsed for a few reasons, mainly because of the following:
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 reason, you just need to modify the MY.CNF to enlarge the Max_allowed_packet value.
There is also a possibility for some reason to timeout, such as the use of singleton in the process of getting a database connection, although the database is connected multiple times, but in fact, the same connection is used, and a two times in the program of the operation of the database Time exceeded wait_timeout (show Status to see this setting), 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 knows it is not a person in the fight.
Troubleshoot MySQL server has gone away

1, applications (such as PHP) for a long time to execute the batch of MySQL statements. The most common is acquisition or new and old data conversion.

Solution:

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

wait_timeout=2880000
Interactive_timeout = 2880000
A specific description of two variables can be Google or read the Official Handbook. If you cannot modify MY.CNF, you can set up client_interactive when you connect to the database, for example:

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:

Inmy.cnfAdd or modify the following variables in the file:

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

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

MySQL: Spooky MySQL server has gone away and its resolution

Perform show status in MySQL, usually focusing more on caching effects, processes, and so on, often ignoring two values:

Variable_name Value
Aborted_clients 3792
Aborted_connects 376


Usually only accounts for the 0.0x% of query, so it is not valued. And in the traditional Web application, query error on the user's impact is not very small, just refresh the page on the OK. In the recent basic transformation, many applications are run as service, unable to prompt the user to refresh, in this case, may affect the quality of service.

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

The most common reason for the "MySQL server has gone away error is" that the "Server timed out and closed the connection.

Some common reasons for the MySQL server has gone away error are:

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

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

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

You are 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 problem of the timeout may solve.

You are have encountered a timeout on the server side and the automatic in the client is reconnection (the disabled 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 is" some cases MySQL doesn ' t get "an error from the" OS when "writing to" TCP/IP connection To the server, but instead gets of the error when trying to read the answer from the connection.

In this case, 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 has been a long time since the last query (th IS is what MYODBC does) or set wait_timeout on the MYSQLD server so high this it in practice the times out.

You can also get this errors if you send a query to the server this is incorrect or too large. If Mysqld receives a packet it too large or out of order, it assumes this something has gone with the client D Closes the connection. If you are need big queries (for example, if you are are working with a 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. You could also need to increase the maximum packet size in the client end. More information on setting the packet size is 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.

Also get a lost connection if to are sending a packet 16MB or larger if your client is older than 4.0.8 and your Er is 4.0.8 and above, or the other way around.

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

You may also to the MySQL server has gone away error if MySQL is started with the--skip-networking option.

Another Networking issue can cause this error occurs if the MySQL port (default 3306) are blocked by your firewall, th US preventing any connections in all to the MySQL server.

can also encounter this error with applications which 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 the each child process.

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



On this basis, there may be 3 reasons:

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

2,mysql server-side configuration is defective or not optimized

3, need to improve the program script

By replacing multiple server and client versions, it is found that only partially reducing the error, and not fully resolved. Excluding 1.

The service side has been thoroughly optimized, and the ideal result has not been achieved. On the timeout setting, from 10 of the experience value to the PHP default of 60, several attempts were made. The MySQL official default (8 hours) is obviously not possible. Thus the exclusion of 2 was also made. (More optimized experience sharing, will be provided after finishing)

For 3 of the program Code Analysis, the Discovery 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 suggested method of thinking [If you have a script, you just have to issue the query again for the client to do a automatic Reconnection. ]。 The actual analysis found that if (! $conn) is not reliable, the program passed the IF (! $conn) After the test, will still return the above error.

The program has been overwritten:

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

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

mysql_query ($sql, $conn);

After the actual observation, the MySQL server has gone away the error basically solves.

BTW: With a question about reconnect,

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

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

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

Mysql_close ($conn);

$conn =mysql_connect (...);

The returned $conn can be used normally. The reason is not clear. No in-depth studies have been done and no discussion has been made. Perhaps the official MySQL bug report will have it.

~ ~ Oh ~ ~


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 defaults to the maximum processing is 1MB
This problem can occur if you use large text or BLOB data in SQL. Comments on the PHP manual

When trying to INSERT or UPDATE and trying to put a large amount of the text or data (BLOB) into a MySQL table for you might run I nto problems.
In Mysql.err you might:
Packet too Large (73904)
To fix your just have to start up MySQL with the Option-o max_allowed_packet=maxsize
You are 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 have their own max_allowed_packet variable, so if you want to handle big packets Increase this is the variable both in the client and the server.

If you are are using the MySQL client program, its 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 you are working with a big BLOB columns). For example, to set the variable to 16MB and start the server like this:

Shell> mysqld--max_allowed_packet=16m 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 option file:

[mysqld]max_allowed_packet=16m

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