Two effective ways to solve the problem of MySQL server has gone away

Source: Internet
Author: User
Tags php script

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 when , the MySQL connection timeout was turned off, and a "MySQL server has gone away" error message appears.

for this error prompt I Baidu a bit, about the cause of this error, such as this articleMySQL server has gone away error reason analysis and solution, which said that there are several reasons for this problem, one of which is said above, about how 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 to pass the relevant parameters, so that MySQL has the option to disconnect auto-link (mysql default is not automatically connected), but I test found in PHP's MySQL API does not carry this function, you re-edit the 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

  1. function  ping () {  

  2.      if (!mysql _ping ( $this ->link)) {   

  3.          mysql_close ( $this ->link);  //Note: Be sure to perform the database shutdown first, this is the key   

  4. $this  ->connect ();

  5. }

  6. }


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

  1. $str  =  Span style= "border:medium none; padding:0px; margin:0px ">file_get_contents ( ' http://www.lai18.com ' );   

  2. $db ->ping (); //after the previous page crawl, or cause the database connection to close, check and reconnect   

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

Reference: http://www.lai18.com/content/321592.html

Two effective ways to solve the problem of MySQL server has gone away

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.