global__r – errors in logs

If you’re using WordPress Multisite in a highly scalable environment using HyperDB or LudicrousDB, you may have seen global__r errors in your logs.

Can't select global__r... yada yada yada

https://twitter.com/jason_conroy/status/523701743415083008

The “global” part of “global__r” comes from these database drop-ins defaulting to a “global” dataset if nothing is found or explicitly passed in. The “__r” part comes from looking at databases intended for reading — databases designated as slaves (vs. master databases intended for writing “__w”).

So if a SELECT query is failing, why would that be?

The first and most logical reason is that the database is down. Check to make sure it’s not by attempting to communicate with the database directly via whatever you are most comfortable with (command line, SequalPRO, etc…)

The second most logical reason is that your web server (powering the PHP part of your application) is unable to reach your database server. Check to make sure fail2ban or some other firewall utility hasn’t erroneously blocked things, and then try to manually ping & connect the two servers together to ensure you receive a good response.

2020 update: when updating from MariaDB 10.3 to 10.5, my bind-address configuration was reset to 127.0.0.1 causing remote connections to be denied, landing me back at my own blog post looking for answers.

The final and less obvious reason this will occur, is harder to track down, and I think might be the source of your error log entries if everything else checks out and you’ve made it to this blog post after scouring the web for answers.

There are two queries that run inside of upgrade_network() and populate_options() respectfully, that try to delete all of the transients for a specific site and a specific network. These two queries are unlikely to get caught by the matching regex used to map database table names to what HyperDB or LudicrousDB use to route queries to their respective servers. They look something like this:

DELETE a, b FROM $wpdb->options a, $wpdb->options b

and

DELETE a, b FROM $wpdb->sitemeta a, $wpdb->sitemeta b

If you search all of WordPress, these are the only two places raw queries like this are done, and they’re only ran under specific conditions where WordPress is cleaning up after itself during a database upgrade. This means the conditions are perfect for a surprise entry in your error logs once in a blue moon when you aren’t hand-holding a huge WordPress multisite/multi-network database upgrade.

How do we prevent these, and what’s the repercussion? The solution is probably a regex fix upstream to these plugins and/or WordPress’s WPDB base class to properly match these queries. The repercussion is transients that don’t get deleted, which isn’t usually a huge problem unless it causes the database upgrade to continuously run; if that was the case, you’d have lots of entries in your error logs.

I have a hunch this issue is exacerbated by object caching plugins that store transients in memory and not in the database. In these types of installations, these raw queries are trying to delete data that never would have been there in the first place.

I’ve also been staring at this code back and forth for a few weeks now, and while there are a lot of moving parts, I haven’t identified any data corruption or loss issues, and these queries are properly escaped and prepared, so it’s unlikely HyperDB or LudicrousDB would introduce anything that might be harmful to existing data.

If you have these issues, hopefully this helps you isolate the root cause to identify whether this is a configuration issue, a caching issue, a communication issue, or an issue with the database itself. If you have more info, I’d love to hear about it in the comments below. <3