Shulou(Shulou.com)05/31 Report--

Editor to share with you how to solve the server has gone away problem in MySQL, I believe that most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

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

The reason for this is usually that sql takes too long to operate, or that the data 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 max_allowed_packed, or the data can be inserted in batches in the program.

There are many reasons for this problem. Summarize the online analysis:

Reason one. The MySQL service is down.

The way to determine whether it belongs to this reason is simple: go to the mysql console and check the running time of mysql.

Mysql > show global status like 'uptime'

+-+ +

| | Variable_name | Value |

+-+ +

| | Uptime | 3414707 | |

+-+ +

1 row in set or check the error log of MySQL to see if there is a restart message

If the uptime value is high, the mysql service has been running for a long time. Indicates that the service has not been restarted recently.

If there is no relevant information in the log, and the table name mysql service has not been restarted recently, you can continue to check the following.

Reason two. Mysql connection timed out

That is, a mysql persistent connection did not initiate a new request for a long time, reached the timeout on the server side, and was forcibly closed by server.

After that, when you initiate a query through this connection, an error server has gone away will be reported.

(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 | |

+-+ +

10 rows in set

Wait_timeout is 28800 seconds, that is, mysql links are automatically closed after 28800 seconds of no operation.

Reason three. Mysql requests that the link process be actively kill

This situation is similar to reason 2, except that one is artificial and the other is MYSQL's own action.

Mysql > show global status like 'com_kill'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_kill | 21 | |

+-+ +

1 row in set reason 4. Your SQL statement was too large.

This error also occurs when the result set of a query exceeds max_allowed_packet. The positioning method is to type out the relevant error statements.

Export to a file as select * into outfile to see if the file size exceeds max_allowed_packet. If so, 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)

Modify the 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)

The following is a supplement:

Applications, such as PHP, execute bulk MYSQL statements for a long time. Execute a SQL, but the SQL statement is too large or contains BLOB or longblob fields. For example, the processing of picture data. It is easy to cause MySQL server has gone away.

Today encountered a similar situation, MySQL just said coldly: MySQL server has gone away.

A brief glance at it may be due to the following reasons:

One possibility is that the SQL statement sent is so long that it exceeds the size of the max_allowed_packet. If this is the reason, you just need to modify the my.cnf and increase the value of max_allowed_packet.

There is also a possibility that some reasons lead to timeout. For example, Singleton is used in the program to obtain a database connection. Although the database is connected many times, the same connection is actually used, and the interval between two operations of the database in the program exceeds wait_timeout (SHOW STATUS can see this setting), then there may be a problem. The easiest way to deal with it is to make the wait_timeout bigger, and of course you can mysql_ping () from time to time in the program so that MySQL knows it's not alone.

Resolve MySQL server has gone away

1. Applications (such as PHP) execute batch MYSQL statements for a long time. The most common is collection or conversion of new and old data.

Solution:

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

Wait_timeout=2880000

Interactive_timeout = 2880000

For a specific description of the two variables, you can google or refer to the official manual. If you cannot modify my.cnf, you can set CLIENT_INTERACTIVE when connecting 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 contains BLOB or longblob fields. For example, the processing of picture 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, a website needs to use the WEB page collector function. When a PHP script requests URL, the requested page may be very slow, exceeding the wait-timeout time of mysql. Then, when the content of the page is caught and ready to be inserted into MySQL, it is found that the MySQL connection has timed out, so there is an error prompt like "MySQL server has gone away" to solve this problem. My experience has the following two points, which may be useful to you:

The first method:

Of course, increase your wait-timeout value. This parameter is set in my.cnf (my.ini below the Windows step down). My database load is a little bit higher, so I set the value to 10. (this value is in seconds, which means that when a database connection has no operation within 10 seconds, it will be forcibly closed. I do not use permanent link (mysql_pconnect), I use mysql_connect, about the effect of this wait-timeout you can see in the process list of MySQL (show processlist), you can set this wait-timeout to be larger, such as 300s, hehe, generally speaking, 300 seconds is enough, in fact, you do not have to set it, MySQL defaults to 8 hours. The situation is determined by your server and site.

The second method:

This is also what I personally think is the best way to check the link status of MySQL and make it re-link.

Maybe you all know that there is such a function as mysql_ping. It is said in a lot of materials that the API of this mysql_ping will check whether the database is linked or not, and if it is disconnected, it will try to reconnect, but in the course of my testing, I found that this is not the case and there are conditions. It is necessary to pass the relevant parameters through the C API of mysql_options, so that MYSQL has the option to break the automatic link (MySQL defaults to not automatically connecting). But in my test, I found that the API of PHP's MySQL does not have this function, so you can re-edit the MySQL, hehe. But the mysql_ping function can finally be used, just with a small trick in it:

This is a function in the middle of my database operation class

The code is as follows:

Function ping () {

If (! mysql_ping ($this- > link)) {

Mysql_close ($this- > link); / / Note: database shutdown must be performed first, which is the key

$this- > connect ()

}

}

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

The code is as follows:

$str = file_get_contents ('http://www.jb51.net');

$db- > ping (); / / after crawling the previous web page, it may cause the database connection to be closed, check and reconnect

$db- > query ('select * from table')

The ping () function first checks whether the data connection is normal, and if it is closed, the entire MYSQL instance of the current script is closed, and then reconnected.

After this treatment, the problem such as MySQL server has gone away can be solved very effectively without causing additional overhead to the system.

The above is all the contents of the article "how to solve the server has gone away problem in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.