Solutions for MySQLserverhasgoneaway

This article introduces the solution of MySQLserverhasgoneaway when a large amount of data is stored in the mysql database. here we have summarized four methods and hope you can refer to them.

This article introduces the mysql server has gone away solution when there is a large amount of data stored in the MySQL database. here we have summarized four methods and hope you can refer to them.

Method 1:

Of course, it is to increase your wait-timeout value. this parameter is in my. cnf (my. ini), my 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 (_ pconnect), but 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 3

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:

The code is as follows:
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 it when connecting to the database.

The code is as follows:
CLIENT_INTERACTIVE, for example: SQL = "set interactive_timeout = 24*3600 "; Mysql_real_query (...)

Method 3:

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:

The code is as follows:
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.

The code is as follows:
$ Str = file_get_contents ('http: // www.111cn.net '); $ Db-> ping (); // after the previous web page is crawled, the database connection may be closed, checked, and reconnected. $ Db-> query ('* from table ');

Here, the ping () method is used separately to write a line, just to facilitate understanding. in formal development, you can put this method before the query and deletion operations.
Ping () this method first checks whether the data connection is normal. if it is closed, the MYSQL instance of the current script is closed 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.

Reference Methods for foreign websites

The code is as follows:
-> Setting up this options in my. ini/mysqld: Wait_timeout = 28800 Interactive_timeout = 28800 Max_allowed_packet = 32 M -> Editing. ini: Changing-> mysqli. reconnect = Off to On -> Checking all the versions of mysql I had available + mysql_upgrade -> Playing with php code:

About
Url