Published: March 8, 2018

If you’re like me, you don’t know much about Drupal 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.

Drupal Database API

Drupal 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 Drupal’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 Drupal 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 2018. In Drupal 7, that is the terminology used in the codebase, although in Drupal 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 Drupal community, but I will still use “master/slave” at points in this post since Drupal 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 Drupal 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 Drupal 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 Drupal 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.  Drupal 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 Drupal 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 Drupal 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. Drupal 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 Drupal 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 Drupal 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/modules/contrib/linkchecker/modules/linkchecker_bean/linkchecker_bean.module)   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();
$i = 0; foreach ($missing_links as $url) {$urlhash = drupal_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); drupal_write_record('linkchecker_link',$link);
}
->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 {
->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 Drupal 7, but I have grown accustomed to being able to rollback database transactions in other PHP frameworks. Too bad most module developers don’t integrate a rollback on erroneous database transactions. Drupal 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 Drupal 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 Drupal 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);

$i = 0; foreach ($missing_links as $url) {$urlhash = drupal_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); drupal_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, Drupal 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 module usingDatabase:ignoreTarget('default', 'slave'). There is also a nice note in the comments about how the ignoring works.


function system_init() {
$path = drupal_get_path('module', 'system'); // Add the CSS for this module. These aren't in system.info, because they // need to be in the CSS_SYSTEM group rather than the CSS_DEFAULT group. drupal_add_css($path . '/system.base.css', array('group' => CSS_SYSTEM, 'every_page' => TRUE));
drupal_add_css($path . '/system.admin.css', array('group' => CSS_SYSTEM)); } drupal_add_css($path . '/system.menus.css', array('group' => CSS_SYSTEM, 'every_page' => TRUE));
drupal_add_css($path . '/system.messages.css', array('group' => CSS_SYSTEM, 'every_page' => TRUE)); drupal_add_css($path . '/system.theme.css', array('group' => CSS_SYSTEM, 'every_page' => TRUE));
// Ignore slave database servers for this request.
//
// In Drupal'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 module .info files.
}




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”.