Two effective solutions to the MySQL server has gone away problem: mysqlgone

Two effective solutions to the MySQL server has gone away problem: mysqlgone

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.

For this error prompt, Baidu gave me a look at the cause of the error, such as the analysis of the cause of the error in MySQL server has gone away and the solution, I have explained several reasons for this problem. One of them is the one mentioned above. My experience on how to solve this problem has the following two points, which may be of use to everyone:

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 (mysql_pconnect), and I am using mysql_connect, you can see the effect of this wait-timeout in the MySQL process list (show processlist). 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 to disconnect the automatic connection (MySQL does not automatically connect by default), but I found that this function is not included in the PHP MySQL API during the test. Edit MySQL again, haha. 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 operations class.

 

1. FunctionPing (){

2. If(! Mysql_ping ($ this-> link )){

3. Mysql_close ($ this-> link); // Note: you must first disable the database. this is the key.

4. $ This-> connect ();

5. }

6. }

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

 

 

1. $ Str = file_get_contents ('HTTP: // www.lai18.com ');

2. $ Db-> ping (); // after the previous web page is crawled, the database connection may be closed, checked, and reconnected.

3. $ 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.

Refer:Http://www.lai18.com/content/321592.html