MySQL Server Has Gone Away Fix For Wordpress 3.6
I have been constantly having issues with the bag of XXXX that is Wordpress and I really, really hate relying on someone else's code especially when there is so much I would do to improve it.
Adding new indexes and writing my own plugins for heavy task can only do so much to tweak the performance of a 3rd party CMS system.
However one of the problems I have been having lately is the dreaded
"MySQL Server Has Gone Away" error littering my error log.
The symptoms I have been getting include:
- Trying to load a page but it just spinning away.
- Checking the console with a TOP command to see very low server loads (0.00 to 0.03)
- No Apache / MySQL processes running.
I first thought I had solved the problem some months back when
I de-activated the Apache Caching plugins my server was using.
I did this because of the high number of Apache related errors in the error log files like this:
[Thu Feb 02 16:30:57 2012] [error] (103)Software caused connection abort: cache: error returned while trying to return mem cached data
These errors related to the times I was getting
the slow page loads, high disk swapping and low server loads.
Apache was obviously causing me issues with it's own caching and a restart always fixed it.
As I was using
WP Super Cache there was no need for duplicate caching and there are far too many levels on a LAMP set-up where caching can be enabled.
For me removing the Apache caching seemed to fix the issue, for a while at least.
However I keep getting intermittent issues where the same symptoms are present except instead of Apache errors in the error log I am getting
lots of MySQL Server Has Gone Away errors like this:
[Wed Aug 14 23:37:29 2013] [error] [client 173.203.107.206] WordPress database error MySQL server has gone away for query UPDATE wp_posts SET robotsmeta = '' WHERE ID = 42693 made by WPOMatic->runCron, WPOMatic->processAll, WPOMatic->processCampaign, WPOMatic->processFeed, WPOMatic->processItem, WPOMatic->insertPost, wp_insert_post, do_action('wp_insert_post'), call_user_func_array, RobotsMeta_Admin->robotsmeta_insert_post
Therefore I looked into
the MySQL Server Has Gone Away error and came across the
Robs Note Book workaround for WordPress.
The fix he uses is pretty simple and involves
using a custom wp-db.php file for your WordPress installation.
As this is a core file that handles all database queries it is a bit annoying in that it may need constant updates when new versions come out.
As the highest version of the workaround on his site is for
WordPress 2.8.1 and I was on
WordPress version 3.6 I had to create my own workaround for the file.
However the fix is pretty simple to implement and just involves using a new function with a number of retries for the failed query with a server re-connect between each loop iteration.
Basically all the fix does is something I do in my own projects many times when I encounter,
lock timeouts or deadlocks e.g
retry the query X number of times before quitting with an error.
You can see
one such example I use to handle LOCK TIMEOUTS in MS SQL here.
Therefore if you need to apply the same fix in a future version of WordPress you can just follow these steps yourself. Download the existing copy of
wp-db.php from the wp-includes folder, back it up and then make a copy before applying these changes.
1. In the
db_connect() function that attempts to connect to your database ensure that the
initialquery flags are set on and then off are placed around the first query run on the class initialise. This happens to currently be the $this->set_charset function which sets the connections correct character set.
The code should be wrapped around this function call and also before the $this->select function which selects the database to use.
$this->initialquery=1;
$this->set_charset( $this->dbh );
$this->ready = true;
$this->initialquery=0;
$this->select( $this->dbname, $this->dbh );
2. You need to create the following query and put in the file somewhere. This query attempts to run the query and then retries a number of times with a re-connect to the database in-between.
This is what handles
the "MySQL Server Has Gone Away" error as it
re-connects if the connection is no longer present.
You can change your retries to any number you want. I use 3 re-attempts as I don't see the point of any further retries as if you can't re-connect to your database after 3 goes you certainly have an issue.
function queryWithReconnect($query)
{
// set this to the number of re-attempts you want to try
$maxcount = 3;
$cnt = 1;
// loop until we reach our $maxcount value OR we get a good query result
while($cnt < $maxcount)
{
// close our connection and then re-connect to our database - this is what fixes the MySQL Has Gone Away error
// as if it has gone away we are re-attempting the connection.
@mysql_close($this->dbh);
// re-connect to our database
$this->db_connect();
// re-run our query and store the result in a global variable
$this->result = @mysql_query($query, $this->dbh);
// if we dont have an error from the server quit now, otherwise carry on!
if (!mysql_error($this->dbh))
{
// return 0 so we know the query ran ok
return 0;
}
// if we are here we had an error so increment our $cnt loop counter
$cnt+=1;
}
// we have looped up to our $maxcount number and all attempts at running the query failed so quit with a failure code!
return 1;
}
3. In the main
query() function which
runs ALL queries in WordPress you need to ensure that the call to our new function that re-attempts the query 3 times is placed just after the initial attempt at running the query.
Find the code that
checks for a MySQL error and place our call to the new function inside it and above the code that
clears any insert_id that may have been stored.
// If there is an error then take note of it..
if ( $this->last_error = mysql_error( $this->dbh ) ) {
// If its the first initial query e.g in the db_connect() function OR we have a MySQL error then call our queryWithReconnect
// function until it either passes OR fails after X attempts.
if (($this->initialquery)||($this->queryWithReconnect($query)!=0)) {
// Clear insert_id on a subsequent failed insert.
if ( $this->insert_id && preg_match( '/^\s*(insert|replace)\s/i', $query ) )
$this->insert_id = 0;
$this->print_error();
return false;
}
}
Since putting this re-try code in my
wp-db.php file I have had
no MySQL Gone Away Errors but it is too early to tell if my main issue of low server loads, no processes running and no website activity is solved yet.
However even if it hasn't this is a good trick to use for your own WordPress code.
If you want to download
the latest version of the wp-db.php workaround file which works with
WordPress 3.6 then you can get it from the link below.
Just re-name it from
wp-db.txt to
wp-db.php and then copy it into
the wp-includes folder of your site. Be sure to make a backup first in-case it all goes tits up!
Download WordPress 3.6 Fix For MySQL Server Has Gone Away Error - wp-db.php
http://www.strictly-software.com/scripts/downloads/wp-db.txt
Posts
