MySQL connection issue "How to troubleshoot MySQL connection timeout shutdown"

--mysql connection Issue "How to troubleshoot MySQL connection timeout shutdown"

------------------------------------------------Reprint

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

? 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

? $str = file_get_contents

?? $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 function of the Max_allowed_packet parameter is to control the maximum length of its communication buffer

----------Wait/interactive_timeout

Wait_timeout--refers to the number of seconds MySQL waits before closing a non-interactive connection

Interactive_timeout--refers to the number of seconds MySQL waits before closing an interactive connection (interactive connection such as a connection in the MySQL GUI tool)

Impact on performance:

Wait_timeout:

(1) If the size is set, then the connection is closed very quickly, so that some persistent connections do not work

(2) If the setting is too large, easy to cause the connection to open too long, in show processlist, can see too much sleep state connection, resulting in too many connections error

(3) generally want to wait_timeout as low as possible

Interactive_timeout settings will not have much impact on your Web application