wpdb should reconnect and retry query when "MySQL server has gone away"

[dtc]

Using 2.3.3, here are the type of errors that crop up in error_log everyday.

[25-Jan-2008 08:37:35] WordPress database error MySQL server has gone away for query UPDATE wp_options SET option_value = '0' WHERE option_name = 'doing_cron'
[25-Jan-2008 09:23:19] WordPress database error MySQL server has gone away for query UPDATE wp_options SET option_value = '0' WHERE option_name = 'doing_cron'
[26-Jan-2008 00:03:54] WordPress database error MySQL server has gone away for query UPDATE wp_options SET option_value = '0' WHERE option_name = 'doing_cron'
[26-Jan-2008 00:04:29] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_pingme' LIMIT 1
[26-Jan-2008 00:04:29] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_encloseme' LIMIT 1
[26-Jan-2008 00:04:29] WordPress database error MySQL server has gone away for query SELECT ID FROM wp_posts WHERE CHAR_LENGTH(TRIM(to_ping)) > 7 AND post_status = 'publish'
[26-Jan-2008 00:05:09] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_pingme' LIMIT 1
[26-Jan-2008 00:05:09] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_encloseme' LIMIT 1
[26-Jan-2008 00:05:09] WordPress database error MySQL server has gone away for query SELECT ID FROM wp_posts WHERE CHAR_LENGTH(TRIM(to_ping)) > 7 AND post_status = 'publish'
[26-Jan-2008 00:05:47] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_pingme' LIMIT 1
[26-Jan-2008 00:05:47] WordPress database error MySQL server has gone away for query SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key = '_encloseme' LIMIT 1
[26-Jan-2008 00:05:47] WordPress database error MySQL server has gone away for query SELECT ID FROM wp_posts WHERE CHAR_LENGTH(TRIM(to_ping)) > 7 AND post_status = 'publish'
.................

Not a WordPress error. Please check the mysql server settings to increase either the timeout or the amount of connections that can be made to the mysql server. If you are on a shared host, then consider either WP-Cache or WP Super Cache.

This plugin is designed to help you deal with the "MySQL Server Has Gone Away" error.

WordPress should try to recover from certain kinds of errors from MySQL. This error in particular. There has been much written about this error, including variations in PHP versions.

I know errors of that kind from some sites as well, but I assume that this is a configuration issue.

I suggest to close as wontfix or invalid for the moment.

If you know of a configuration that fixes this then it's a configuration issue. Until then, your assumption is no reason to close this ticket.

Typically this error occurs when the mysql client connection has timed out due to inactivity. This is correct behavior and not necessarily within the power of the WP admin to configure.

It is also easy to recover from: identify the error code, reconnect, and retry the query. I have implemented this in wpdb-based scripts that commonly see deadlocks (another recoverable error). A do-while loop works well.

Well, I'm willing to pick up the configuration argument.

Please provide steps to reproduce the problem so it's possible to look into this issue with more detail.

Write a script that does one query, then sleeps a while, then does another query. Increase the sleep time until the error appears.

This error also appears when wp_options has hit circa 100,000+ rows (on shared and VPS hosting) - I'm not sure if the limitation is from MySQL or simply a "query taking too long to get answer" issue.

Related - if a SQL table has crashed, MySQL has the ability to return that in the error, WP doesn't seem capable of returning that reason - might be worth a look see to check if could be added to verbosity?

Marked #15298 as dup.

This patch checks the connection whenever wpdb::query() is run. This will add a slight delay to each query (approximately the ping time to the MySQL server).

Here's a testing snippet.

global $wpdb;
$wpdb->query( 'SET SESSION wait_timeout = 10;' );
echo $wpdb->get_var( 'select id from wp_posts limit 1;' );
sleep( 12 );
echo $wpdb->get_var( 'select id from wp_posts limit 1,1;' );

Checking the connection before every single query seems wasteful to me.

Ideally, it would work like this:

1. Do query.
2. Check some flag that says if the query actually reached the server.
3. Re-connect, if needed.

A little wasteful.

I'll have another pass and see what the code looks like when reconnecting after a query failed because the server went away.

Patch 3 does the check after a query fails with error 2006 (server gone away).

I also added a 1 second sleep between connection retries, to give the server 5 seconds to come back.

Now that's more like it. :)

I think the actual connection logic, i.e. mysql_connect() + $this->select() should be moved to a helper method, as it's probably duplicated currently.

Good point, patch 4 makes wpdb::db_connect() more generic so the code duplication can be removed.

Also, the way diff created that patch is kind of weird.

I'm game for this.

I discussed with pento how we could clean up check-connection.4.diff​'s while() loop a bit. Otherwise, looks interesting and good.

Cleaned up the loop in wpdb::check_connection(), per discussion with nacin.

The db_connect() call inside check_connection() doesn't pass $allow_bail = false, as I imagine it is supposed to.

New patch adds the correct parameter, updated to patch cleanly against trunk.

I think it's too late in the cycle to mess with such a critical component.