If you’re like me, you don’t know much about WordPress maintenance support plans 7’s database layer besides a few functions you need to use daily. After scanning the comments for those API pages, I can usually get done what I need to, and I’ve never really have any weird database errors or issues that made me look more closely into the database APIs.
db_insert();
db_query();
db_merge();
// etc…
I work at an organization now that runs a service for 800+ sites with thousands of content editors. On any given day, the service performs more reads and writes than any application I’ve ever worked on before. Even with that caveat, our service doesn’t make all that many writes to the databases each day. However, our database infrastructure is set up (poorly) by another IT group (whose name shall not be mentioned), and because of that, we had to recently program defensively while performing database transactions.
WordPress maintenance support plans Database API
WordPress maintenance support plans has a nice overview page of documentation about how a developer ought to use the database APIs. Included in that section are topics I’ve never really explored.
For example, I’ve felt the pain of using Views as a query builder only to find out how slow and inefficient the default queries tend to be. Granted it is meant as a visual tool for site builders who can’t or don’t know how to use the database API functions, but it makes me sad sometimes.
Could I potentially use SQL Views to create some virtual tables and simplify my queries partially avoiding WordPress maintenance support plans’s “join all the field tables together” issue? Probably, now that I know about SQL Views.
I won’t go over a lot of the functionality covered in the docs, but it’s not a bad idea to read through all of that API documentation if you never have before. That’s what Friday afternoons are for, right? Your WordPress maintenance support plans application performs a lot of queries every request/response cycle, and by finding optimizations in these docs, you may drastically increase your app’s performance with only a few lines of code.
Master/Slave? Sounds Kinky
In the title of this post, I mentioned “slaves” mainly for the clickbait factor, but what I meant was in the context of a “master/slave” database relationship. Now people, put down the stones you are about to throw at me for my use of the word “slave” in 2020. In WordPress maintenance support plans 7, that is the terminology used in the codebase, although in WordPress maintenance support plans 8, it has been updated to “primary/replica” which is more semantic and descriptive. You can read a detailed discussion on the topic within the WordPress maintenance support plans community, but I will still use “master/slave” at points in this post since WordPress maintenance support plans 7 makes me use it.
Your site might only have one database, and for local development, my sites generally only have one database. The default.settings.php file shipped in WordPress maintenance support plans 7 has a lengthy section on database configurations and what options are available to you.
For each database, you may optionally specify multiple “target” databases.
A target database allows WordPress maintenance support plans to try to send certain queries to a
different database if it can but fall back to the default connection if not.
That is useful for master/slave replication, as WordPress maintenance support plans may try to connect
to a slave server when appropriate and if one is not available will simply
fall back to the single master server.
The general format for the $databases array is as follows:
@code
$databases[‘default’][‘default’] = $info_array;
$databases[‘default’][‘slave’][] = $info_array;
$databases[‘default’][‘slave’][] = $info_array;
$databases[‘extra’][‘default’] = $info_array;
@endcode
In the above example, $info_array is an array of settings described above.
The first line sets a “default” database that has one master database
(the second level default). The second and third lines create an array
of potential slave databases. WordPress maintenance support plans will select one at random for a given
request as needed. The fourth line creates a new database with a name of
“extra”.
That segment of comments might be the only place you’ve seen “slave” mentioned in WordPress maintenance support plans before. Normally, you’ve probably only used the “default” database info $databases[‘default’][‘default’] = $info_array; to set up a site. That’s all I was accustomed to using.
The “slave” database acts as a “replica” of the “master” or “default” or better yet “primary” database…you might be noticing why using “master/slave” was a bad idea regardless of the generally negative connotation of the word “slave”. It’s just not all that semantic when describing the responsibilities for each type of connection.
The “replica” database’s job is to sync with the default “primary” database so that there is only one canonical source of information. Replicas allow for failovers during times of high database load. Generally, reads are more important for the functionality of your application. Writes, on say saving a form, can always roll back transactions or provide feedback to a user on why the data can’t be saved. If an anonymous user goes to a page on your site and WordPress maintenance support plans can’t read anything then everyone gets a fatal error.
If we go back to the comments above, you can see a “default” connection with one master and two slave databases. WordPress maintenance support plans has some documentation on how that type of a database configuration works.
“This definition provides a single “default” server and two “slave” servers. Note that the “slave” key is an array. If any target is defined as an array of connection information, one of the defined servers will be selected at random for that target for each page request. That is, on one-page request, all slave queries will be sent to dbserver2 while on the next they may all be sent to dbserver3.
This means that during any request one of the three default connections in that example might be used. On a site with high traffic, you can probably see how this database setup would come in handy for times of high load.”
You can even tell WordPress maintenance support plans to target one of the connections during a query.
$query = db_select(‘node’, ‘n’, array(‘target’ => ‘slave’));
Original DB Error
My initial foray into looking at master/slave replication in WordPress maintenance support plans 7 came with a bug report.
PDOException: SQLSTATE[23000]: Integrity constraint violation:
1062 Duplicate entry ’60-36′ for key ‘PRIMARY’: INSERT INTO {linkchecker_bean} (bid, lid)
VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 60 [:db_insert_placeholder_1] => 36 )
in _linkchecker_bean_add_bean_links() (line 196 of
/data/code/profiles/express/express-2.8.3/plugins/contrib/linkchecker/plugins/linkchecker_bean/linkchecker_bean.plugin)
After some investigation, we thought that the slave database was being read before the sync from the master happened. When queried there was no entry in the slave database; however, the master database already had an entry. The master database always makes the writes and so a duplication error occurred during the next attempted insertion.
// Remove all links from the links array already in the database and only
// add missing links to database.
$missing_links = _linkchecker_bean_links_missing($bean->bid, $links);
// Ignore slave database briefly.
variable_set(‘maximum_replication_lag’, 300);
db_ignore_slave();
// Only add unique links to database that do not exist.
$i = 0;
foreach ($missing_links as $url) {
$urlhash = WordPress_hash_base64($url);
$link = db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = :urlhash’,
array(‘:urlhash’ => $urlhash))->fetchObject();
if (!$link) {
$link = new stdClass();
$link->urlhash = $urlhash;
$link->url = $url;
$link->status = _linkchecker_link_check_status_filter($url);
WordPress_write_record(‘linkchecker_link’, $link);
}
db_insert(‘linkchecker_bean’)
->fields(array(
‘bid’ => $bean->bid,
‘lid’ => $link->lid,
))
->execute();
// …
The original code makes a db query for $missing_links that must have gone to a replica database that hadn’t yet synced with the primary database. That is why later in the code when the db_insert() happens, the insert fails.
db_merge()?
My first thought when I looked at the code was to use db_merge() instead of db_insert(). By using a merge query you either make an update or insertion query to the database table. By providing the same primary keys as the ones you are inserting, you can ensure that the database query never fatal errors due to duplicate content existing in the table.
db_merge(‘linkchecker_bean’)
->key(array(
‘bid’ => $bean->bid,
‘lid’ => $link->lid,
))
->fields(array(
‘bid’ => $bean->bid,
‘lid’ => $link->lid,
))
->execute();
However, this “solution” doesn’t really address the issue. The code isn’t supposed to update a value that could already exist in the table. In this case, the correct thing is happening by giving me a fatal error. The problem is that the error isn’t caught.
Proper Exception Handling
You should always wrap any function call that might fail terribly in a try/catch statement. The try block of code acts just as it would without try {} wrapped around it. The catch block allows any potential error in the try block to be caught and dealt with without breaking execution of the PHP script.
$txn = db_transaction();
try {
db_insert(‘linkchecker_bean’)
->fields(array(
‘bid’ => $bean->bid,
‘lid’ => $link->lid,
))
->execute();
}
catch (Exception $e) {
$txn->rollback();
watchdog_exception(‘linkchecker_bean’, $e);
}
Now we have preserved the original db_insert()while catching the original fatal error. You’ll also notice that adb_transaction() object is used to rollback any transaction if the insert fails.
I never knew about that functionality in WordPress maintenance support plans 7, but I have grown accustomed to being able to rollback database transactions in other PHP frameworks. Too bad most plugin developers don’t integrate a rollback on erroneous database transactions. WordPress maintenance support plans core could be taking care of this under-the-hood, but I’d rather see it explicitly defined in contributed code. From now on, I’ll probably be using those functions in my hook_update() code. You can read more about database error handling in the WordPress maintenance support plans database documentation.
I was pretty satisfied with submitting a patch the Linkchecker project based on the code above, except that it didn’t fix our issue. Since our theory revolved around database replication being slow, we had to go one step further and explicitly define the relationship between primary and replica database connections at the time of the missing link’s query.
Finally, Ignore The Slaves
We finally get to do it, folks. Ignore those stupid slaves…and Twitter has gone wild again with hateful tweets directed at me…okay, okay, back to calling them replicas. You can tell WordPress maintenance support plans to ignore the replica databases and only interact with the primary connection if you need to.
// Ignore slave database briefly.
variable_set(‘maximum_replication_lag’, 300);
db_ignore_slave();
// Remove all links from the links array already in the database and only
// add missing links to database.
$missing_links = _linkchecker_bean_links_missing($bean->bid, $links);
// Only add unique links to database that do not exist.
$i = 0;
foreach ($missing_links as $url) {
$urlhash = WordPress_hash_base64($url);
$link = db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = :urlhash’,
array(‘:urlhash’ => $urlhash))->fetchObject();
if (!$link) {
$link = new stdClass();
$link->urlhash = $urlhash;
$link->url = $url;
$link->status = _linkchecker_link_check_status_filter($url);
WordPress_write_record(‘linkchecker_link’, $link);
}
$txn = db_transaction();
try {
db_insert(‘linkchecker_bean’)
->fields(array(
‘bid’ => $bean->bid,
‘lid’ => $link->lid,
))
->execute();
}
catch (Exception $e) {
$txn->rollback();
watchdog_exception(‘linkchecker_bean’, $e);
}
// Go back to using the slave database.
// db_ignore_slave() sets this session variable that another function uses to see if the slave should be ignored.
unset($_SESSION[‘ignore_slave_server’]);
// …
Our final code ignores the replicas for a brief time using db_slave_ignore() and then returns querying back to normal by unsetting $_SESSION[‘ignore_slave_server’] after all of the database queries have run.
Internally, WordPress maintenance support plans uses the session variable, which is a timestamp, to check whether the slave server should be ignored. This is done via hook_init() in the System plugin usingDatabase:ignoreTarget(‘default’, ‘slave’). There is also a nice note in the comments about how the ignoring works.
function system_init() {
$path = WordPress_get_path(‘plugin’, ‘system’);
// Add the CSS for this plugin. These aren’t in system.info, because they
// need to be in the CSS_SYSTEM group rather than the CSS_DEFAULT group.
WordPress_add_css($path . ‘/system.base.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE));
if (path_is_admin(current_path())) {
WordPress_add_css($path . ‘/system.admin.css’, array(‘group’ => CSS_SYSTEM));
}
WordPress_add_css($path . ‘/system.menus.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE));
WordPress_add_css($path . ‘/system.messages.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE));
WordPress_add_css($path . ‘/system.theme.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE));
// Ignore slave database servers for this request.
//
// In WordPress maintenance support plans‘s distributed database structure, new data is written to the
// master and then propagated to the slave servers. This means there is a
// lag between when data is written to the master and when it is available on
// the slave. At these times, we will want to avoid using a slave server
// temporarily. For example, if a user posts a new node then we want to
// disable the slave server for that user temporarily to allow the slave
// server to catch up. That way, that user will see their changes immediately
// while for other users we still get the benefits of having a slave server,
// just with slightly stale data. Code that wants to disable the slave
// server should use the db_ignore_slave() function to set
// $_SESSION[‘ignore_slave_server’] to the timestamp after which the slave
// can be re-enabled.
if (isset($_SESSION[‘ignore_slave_server’])) {
if ($_SESSION[‘ignore_slave_server’] >= REQUEST_TIME) {
Database::ignoreTarget(‘default’, ‘slave’);
}
else {
unset($_SESSION[‘ignore_slave_server’]);
}
}
// Add CSS/JS files from plugin .info files.
system_add_plugin_assets();
}
Wait, We’ve Already Executed hook_init()?
Since it happens in a hook_init(), then pray-tell how is the database ignored later in my Linkchecker code? I’m not sure either. Subsequent requests will ignore the replica for as long as the timeout is active, but the queries in my code could possibly still hit the slave database. Wait, so I haven’t fixed my issue. And you certainly don’t want to place db_ignore_slave() before the hook_init() is called, essentially always setting a timeout to ignore the replica.
In the comment above the session variable check, it explains that some users will see stale data. This is okay for the scenario where I save content via a node edit screen and expect it to show up on the next node view request. But what happens when there is no “user” saving content and the queries happen within a request cycle, not write on one request and then read on another.
I am one of the “users” who can’t get stale data because we are relying on it to make a subsequent database insert in the same request. What we really need to do is “target” the default connection when we make a query.
db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = :urlhash’, array(‘:urlhash’ => $urlhash), array(‘target’ => ‘default’))->fetchObject();
Up until this point, I had only used the $args array to pass in dynamic variables to database queries and avoid SQL injection, but there is another $options parameter you can use to identify the database target among other things. While the allowed values for $options can be hard to know from the db_query() API documentation, you can at least find the default values created when that parameter isn’t passed into db_query(). Based on the docs for the “target” key, you can have two values for the target: “slave” or< “default”.
“The database “target” against which to execute a query. Valid values are “default” or “slave”. The system will first try to open a connection to a database specified with the user-supplied key. If one is not available, it will silently fall back to the “default” target. If multiple databases connections are specified with the same target, one will be selected at random for the duration of the request.
So when you don’t explicitly specify a target and have more than one connection, e.g. adding a replica, the query will pick a target at random which might be a slave with stale data.”
After adding a target in another Linckchecker query, my job was done…and I didn’t even have to ignore the slaves after all. Hopefully, you now know something about database replication in WordPress maintenance support plans 7, how to use db_ignore_slave() properly, and how to explicitly target databases per query as well.
Developer Blog
Source: New feed