Tell you how to solve the problem of MySQL server has gone away

Recently, a website was designed to use the WEB page collector function. When a PHP script requests a URL, the requested webpage may be slow, exceeding the wait-timeout time of mysql, then, when the webpage content is captured and you are about to insert it to MySQL, you will find that the MySQL connection times out and is closed, so an error message such as "MySQL server has gone away" appears, to solve this problem, my experience may be helpful to you in the following two aspects:

Method 1:

Of course, it is to increase your wait-timeout value. This parameter is in my. cnf (my. ini), my database load is slightly larger, so the value I set is 10, the unit of this value is seconds, this means that when a database connection does not perform any operation within 10 seconds, it will be forcibly closed. I am not using a permanent link to mysql_pconnect). mysql_connect is used, for the wait-timeout effect, you can see show processlist In the MySQL process list). You can set this wait-timeout to a larger value, such as 300 seconds, generally, 300 seconds is enough. In fact, you do not need to set it. The default value of MySQL is 8 hours. The situation depends on your server and site.

Method 2:

This is also the best method I personally think, that is, to check the connection status of MySQL and reconnect it.

We may all know that there is a function like mysql_ping. In many materials, this mysql_ping API will check whether the database is connected. If it is disconnected, it will try again, however, I found that this is not the case in my test. It is conditional and must pass relevant parameters through the C API mysql_options, MYSQL has the option of disconnecting automatic connections. MySQL does not automatically connect to MySQL by default. But I found that this function is not included in the PHP MySQL API during the test. Edit MYSQL again. However, the mysql_ping function can still be used at last, but there is a small operation skill in it:

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

 
 

  
  
Function ping (){
  
  
 
  
  
If (! Mysql_ping ($ this-> link )){
  
  
 
  
  
Mysql_close ($ this-> link); // Note: you must first disable the database. this is the key.
  
  
 
  
  
$ This-> connect ();
  
  
 
  
  
}
  
  
 
  
  
}
 
 

The code that I need to call this function may be like this.

 
 

  
  
$ Str = file_get_contents ('HTTP: // www.tianqiyugao.net ');
  
  
 
  
  
$ Db-> ping (); // after the previous web page is crawled, the database connection may be closed, checked, and reconnected.
  
  
 
  
  
$ Db-> query ('select * from table ');
 
 

Ping () This function first checks whether the data connection is normal. If it is closed, it closes the MYSQL instance of the current script and then reconnects.

After such processing, it can effectively solve problems such as MySQL server has gone away without causing additional overhead to the system.

In a similar situation today, MySQL is just a cold saying: MySQL server has gone away.

You probably browsed it for the following reasons:

One possibility is that the SQL statement sent is too long and exceeds the size of max_allowed_packet. For this reason, you only need to modify my. cnf and increase the value of max_allowed_packet.

There is another possibility that timeout may be caused due to some reasons. For example, Singleton is used in the program to obtain the database connection. Although the program connects to the database multiple times, it actually uses the same connection, in addition, if the interval between two database operations in the program exceeds wait_timeoutSHOW STATUS, you can see this setting), then problems may occur. The simplest way to deal with it is to increase wait_timeout. Of course, you can also use mysql_ping () in the program from time to time, so that MySQL knows that it is not a battle.

Solve MySQL server has gone away

1. applications such as PHP) execute MYSQL statements in batches 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 details about the two variables, refer to google or the official manual. If you cannot modify my. cnf, you can set CLIENT_INTERACTIVE when connecting to the database. For example:

sql = "set interactive_timeout=24*3600";

mysql_real_query(...)

2. Execute an SQL statement, but the SQL statement is too large or the statement contains BLOB or longblob fields. For example, processing image data

Solution:

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

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

max_allowed_packetThe parameter is used to control the maximum length of the Communication buffer.