Call us Toll-Free:
1-800-218-1525
Email us

 Sponsors

MySQL Server Has Gone Away (Error 2006)

Mike Peters, 08-30-2007
If you're doing any heavy-duty MySQL programming, you've probably seen this dreaded error message before:

MySQL Server Has Gone Away

Typically by the time you see it, something has gone terribly wrong and your output page is either blank or your server-side script completely crashed.

Developers need to understand a MySQL connection is an open socket between two machines. It is not guaranteed the socket will stay alive. In fact, you should accept it as a fact that this socket will die unannounced and you must plan ahead by implementing rigorous error checking in your code.

Most common causes for a MySQL Server Has Gone Away message

1. The server timed out and closed the connection. (Default wait_timeout is 8 hours. Don't even think about increasing it. In fact, in a high-load environment you should decrease this timeout to less than a minute)

2. You issued a “close” on your MySQL connection and then tried to run a query on the closed connection. (Are you that stupid? Clean your code)

3. You got a timeout from the TCP/IP connection on the client side. This may happen if you have been using the commands: mysql_options(…, MYSQL_OPT_READ_TIMEOUT,…) or mysql_options(…, MYSQL_OPT_WRITE_TIMEOUT,…).

4. You sent a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries set the server’s max_allowed_packet variable

5. An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

6. Prior to MySQL 5.0.19, even if the reconnect flag in the MYSQL structure is equal to 1, MySQL does not automatically reconnect and re-issue the query as it doesn’t know if the server already completed the query or not.

7. Hostname lookups fail (for example, if the DNS server on which your server or network relies goes down).

How to fix
"MySQL Server Has Gone Away" error scenarios

Once you've implemented the obvious steps as suggested above, you should take a deep breath and realize regardless of what you do, MySQL connection can and will terminate unexpectedly, at the most inconvenient time.

It could happen while a user is viewing your WordPress blog, or while you're running a critical cronjob collecting stats, or any other time when you least expect it.

The solution?

Face the reality and deal with it. Check the return value of mysql_query() in your code and if MySQL has gone away, re-connect and retry the query until successful.

It is amazing how many times I see code like this -


.
.
.
$query = "UPDATE stats SET something=1";
if (!
mysql_query($query)) die("failed");
.
.
.

The code above is terrible because at any given point in time, MySQL can go away, completely breaking the flow of this script.

A much more elegant implementation that handles MySQL connection dying on you, would be:


.
.
.
$query = "UPDATE stats SET something=1";
safe_mysql_query($query);
.
.
.
function
safe_mysql_query($query)
{
  do
  {
 
// Init
 
$bMySQLGone = 0;

 
// Execute query
 
$res = mysql_query($query);

 
// If failed
 
if (!$res)
  {
   
// If the reason we failed is because MySQL has gone away
   
if (mysql_errno() == 2006)
    {
   
// Record MySQL dying on us, so that we try the query again
   
$bMySQLGone = 1;

   
// Attempt re-connecting to database
    // (you'll have to write this function yourself)
   
safe_mysql_pconnect();
    }
  }

  } while (!
$bMySQLGone);

 
// Return result
 
return $res;
}


So stop slacking on your error-checking and ALWAYS test the return-result of mysql_query, so that your scripts are rock solid.

Implementing a database-layer for all database functions is always a good idea and it makes handling this type of error a breeze.
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions