If you happen to’re like me, you don’t know a lot about 7’s database layer in addition to just a few features you have to use day by day. After scanning Drupal Development Company feedback for these API pages, I can normally get completed what I must, and I’ve by no means actually have any bizarre database errors or points that made me look extra carefully into Drupal Development Company database APIs. db_insert(); db_query(); db_merge(); // and many others… I work at a company now that runs a service for 800+ websites with hundreds of content material editors. On any given day, Drupal Development Company service performs extra reads and writes than any utility I’ve ever labored on earlier than. Even with that caveat, our service doesn’t make all that many writes to Drupal Development Company databases every day. Nevertheless, our database infrastructure is ready up (poorly) by one other IT group (whose identify shall not be talked about), and due to that, we needed to just lately program defensively whereas performing database transactions. Database API has a pleasant overview web page of documentation about how a developer ought to make use of Drupal Development Company database APIs. Included in that part are subjects I’ve by no means actually explored. For instance, I’ve felt Drupal Development Company ache of utilizing Views as a question builder solely to learn the way gradual and inefficient Drupal Development Company default queries are typically. Granted it’s meant as a visible software for website builders who can’t or don’t know how you can use Drupal Development Company database API features, however it makes me unhappy generally. Might I probably use SQL Views to create some digital tables and simplify my queries partially avoiding ’s “be part of all Drupal Development Company area tables collectively” concern? In all probability, now that I find out about SQL Views. I gained’t go over numerous Drupal Development Company performance lined in Drupal Development Company docs, however it’s not a nasty thought to learn by all of that API documentation for those who by no means have earlier than. That’s what Friday afternoons are for, proper? Your utility performs numerous queries each request/response cycle, and by discovering optimizations in these docs, you could drastically improve your app’s efficiency with just a few traces of code. Grasp/Slave? Sounds Kinky In Drupal Development Company title of this publish, I discussed “slaves” primarily for Drupal Development Company clickbait issue, however what I meant was in Drupal Development Company context of a “grasp/slave” database relationship. Now folks, put down Drupal Development Company stones you’re about to throw at me for my use of Drupal Development Company phrase “slave” in 2021. In 7, that’s Drupal Development Company terminology utilized in Drupal Development Company codebase, though in 8, it has been up to date to “main/reproduction” which is extra semantic and descriptive. You’ll be able to learn an in depth dialogue on Drupal Development Company subject inside Drupal Development Company group, however I’ll nonetheless use “grasp/slave” at factors on this publish since 7 makes me use it. Your website may solely have one database, and for native development, my websites typically solely have one database. Drupal Development Company default.settings.php file shipped in 7 has a prolonged part on database configurations and what choices can be found to you. For every database, you could optionally specify a number of “goal” databases. A goal database permits to attempt to ship sure queries to a completely different database if it will possibly however fall again to Drupal Development Company default connection if not. That’s helpful for grasp/slave replication, as might attempt to join to a slave server when acceptable and if one will not be accessible will merely fall again to Drupal Development Company single grasp server. Drupal Development Company normal format for Drupal Development Company $databases array is as follows Drupal 10 Upkeep and Help Service @code $databases[‘default’][‘default’] = $info_array; $databases[‘default’][‘slave’][] = $info_array; $databases[‘default’][‘slave’][] = $info_array; $databases[‘extra’][‘default’] = $info_array; @endcode In Drupal Development Company above instance, $info_array is an array of settings described above. Drupal Development Company first line units a “default” database that has one grasp database (Drupal Development Company second stage default). Drupal Development Company second and third traces create an array of potential slave databases. will choose one at random for a given request as wanted. Drupal Development Company fourth line creates a brand new database with a reputation of “additional”. That section of feedback may be Drupal Development Company solely place you’ve seen “slave” talked about in earlier than. Usually, you’ve in all probability solely used Drupal Development Company “default” database data $databases[‘default’][‘default’] = $info_array; to arrange a website. That’s all I used to be accustomed to utilizing. Drupal Development Company “slave” database acts as a “reproduction” of Drupal Development Company “grasp” or “default” or higher but “main” database…you may be noticing why utilizing “grasp/slave” was a nasty thought no matter Drupal Development Company typically damaging connotation of Drupal Development Company phrase “slave”. It’s simply not all that semantic when describing Drupal Development Company obligations for every sort of connection. Drupal Development Company “reproduction” database’s job is to sync with Drupal Development Company default “main” database so that there’s just one canonical supply of knowledge. Replicas permit for failovers throughout occasions of excessive database load. Typically, reads are extra necessary for Drupal Development Company performance of your utility. Writes, on say saving a type, can at all times roll again transactions or present suggestions to a consumer on why Drupal Development Company information can’t be saved. If an nameless consumer goes to a web page in your website and can’t learn something then everybody will get a deadly error. If we return to Drupal Development Company feedback above, you may see a “default” reference to one grasp and two slave databases. has some documentation on how that sort of a database configuration works. “This definition gives a single “default” server and two “slave” servers. Notice that Drupal Development Company “slave” secret is an array. If any goal is outlined as an array of connection info, one among Drupal Development Company outlined servers can be chosen at random for that focus on for every web page request. That’s, on one-page request, all slave queries can be despatched to dbserver2 whereas on Drupal Development Company subsequent they might all be despatched to dbserver3. Which means throughout any request one among Drupal Development Company three default connections in that instance may be used. On a website with excessive site visitors, you may in all probability see how this database setup would turn out to be useful for occasions of excessive load.” You’ll be able to even inform to focus on one among Drupal Development Company connections throughout a question. $question = db_select(‘node’, ‘n’, array(‘goal’ => ‘slave’)); Authentic DB Error My preliminary foray into taking a look at grasp/slave replication in 7 got here with a bug report. PDOException Drupal 10 Upkeep and Help Service SQLSTATE[23000] Drupal 10 Upkeep and Help Service Integrity constraint violation Drupal 10 Upkeep and Help Service 1062 Duplicate entry ’60-36′ for key ‘PRIMARY’ Drupal 10 Upkeep and Help Service INSERT INTO {linkchecker_bean} (bid, lid) VALUES ( Drupal 10 Upkeep and Help Servicedb_insert_placeholder_0, Drupal 10 Upkeep and Help Servicedb_insert_placeholder_1); Array ( [ Drupal 10 Maintenance and Support Servicedb_insert_placeholder_0] => 60 [ Drupal 10 Maintenance and Support Servicedb_insert_placeholder_1] => 36 ) in _linkchecker_bean_add_bean_links() (line 196 of /information/code/profiles/specific/express-2.8.3/Drupal 10 modules/contrib/linkchecker/Drupal 10 modules/linkchecker_bean/linkchecker_bean.Drupal 10 module) After some investigation, we thought that Drupal Development Company slave database was being learn earlier than Drupal Development Company sync from Drupal Development Company grasp occurred. When queried there was no entry in Drupal Development Company slave database; nonetheless, Drupal Development Company grasp database already had an entry. Drupal Development Company grasp database at all times makes Drupal Development Company writes and so a duplication error occurred throughout Drupal Development Company subsequent tried insertion. // Take away all hyperlinks from Drupal Development Company hyperlinks array already in Drupal Development Company database and solely // add lacking hyperlinks to database. $missing_links = _linkchecker_bean_links_missing($bean->bid, $hyperlinks); // Ignore slave database briefly. variable_set(‘maximum_replication_lag’, 300); db_ignore_slave(); // Solely add distinctive hyperlinks to database that don’t exist. $i = 0; foreach ($missing_links as $url) { $urlhash = Drupal 10_hash_base64($url); $hyperlink = db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = Drupal 10 Upkeep and Help Serviceurlhash’, array(‘ Drupal 10 Upkeep and Help Serviceurlhash’ => $urlhash))->fetchObject(); if (!$hyperlink) { $hyperlink = new stdClass(); $link->urlhash = $urlhash; $link->url = $url; $link->standing = _linkchecker_link_check_status_filter($url); Drupal 10_write_record(‘linkchecker_link’, $hyperlink); } db_insert(‘linkchecker_bean’) ->fields(array( ‘bid’ => $bean->bid, ‘lid’ => $link->lid, )) ->execute(); // … Drupal Development Company authentic code makes a db question for $missing_links that will need to have gone to a duplicate database that hadn’t but synced with Drupal Development Company main database. That’s the reason later in Drupal Development Company code when Drupal Development Company db_insert() occurs, Drupal Development Company insert fails. db_merge()? My first thought once I checked out Drupal Development Company code was to make use of db_merge() as an alternative of db_insert(). By utilizing a merge question you both make an replace or insertion question to Drupal Development Company database desk. By offering Drupal Development Company similar main keys as Drupal Development Company ones you’re inserting, you may be certain that Drupal Development Company database question by no means deadly errors as a result of duplicate content material current in Drupal Development Company desk. db_merge(‘linkchecker_bean’) ->key(array( ‘bid’ => $bean->bid, ‘lid’ => $link->lid, )) ->fields(array( ‘bid’ => $bean->bid, ‘lid’ => $link->lid, )) ->execute(); Nevertheless, this “resolution” doesn’t actually deal with Drupal Development Company concern. Drupal Development Company code isn’t speculated to replace a worth that might exist already in Drupal Development Company desk. On this case, Drupal Development Company appropriate factor is going on by giving me a deadly error. Drupal Development Company downside is that Drupal Development Company error isn’t caught. Correct Exception Dealing with You need to at all times wrap any operate name that may fail terribly in a attempt/catch assertion. Drupal Development Company attempt block of code acts simply as it will with out attempt {} wrapped round it. Drupal Development Company catch block permits any potential error in Drupal Development Company attempt block to be caught and handled with out breaking execution of Drupal Development Company PHP script. $txn = db_transaction(); attempt { 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’ve got preserved Drupal Development Company authentic db_insert()whereas catching Drupal Development Company authentic deadly error. You’ll additionally discover that adb_transaction() object is used to rollback any transaction if Drupal Development Company insert fails. I by no means knew about that performance in 7, however I’ve grown accustomed to with the ability to rollback database transactions in different PHP frameworks. Too dangerous most Drupal 10 module builders don’t combine a rollback on inaccurate database transactions. core might be caring for this under-the-hood, however I’d reasonably see it explicitly outlined in contributed code. Any longer, I’ll in all probability be utilizing these features in my hook_update() code. You’ll be able to learn extra about database error dealing with in Drupal Development Company database documentation. I used to be fairly happy with submitting a patch Drupal Development Company Linkchecker venture primarily based on Drupal Development Company code above, besides that it didn’t repair our concern. Since our concept revolved round database replication being gradual, we needed to go one step additional and explicitly outline Drupal Development Company relationship between main and reproduction database connections at Drupal Development Company time of Drupal Development Company lacking hyperlink’s question. Lastly, Ignore Drupal Development Company Slaves We lastly get to do it, of us. Ignore these silly slaves…and Twitter has gone wild once more with hateful tweets directed at me…okay, okay, again to calling them replicas. You’ll be able to inform to disregard Drupal Development Company reproduction databases and solely work together with Drupal Development Company main connection if you have to. // Ignore slave database briefly. variable_set(‘maximum_replication_lag’, 300); db_ignore_slave(); // Take away all hyperlinks from Drupal Development Company hyperlinks array already in Drupal Development Company database and solely // add lacking hyperlinks to database. $missing_links = _linkchecker_bean_links_missing($bean->bid, $hyperlinks); // Solely add distinctive hyperlinks to database that don’t exist. $i = 0; foreach ($missing_links as $url) { $urlhash = Drupal 10_hash_base64($url); $hyperlink = db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = Drupal 10 Upkeep and Help Serviceurlhash’, array(‘ Drupal 10 Upkeep and Help Serviceurlhash’ => $urlhash))->fetchObject(); if (!$hyperlink) { $hyperlink = new stdClass(); $link->urlhash = $urlhash; $link->url = $url; $link->standing = _linkchecker_link_check_status_filter($url); Drupal 10_write_record(‘linkchecker_link’, $hyperlink); } $txn = db_transaction(); attempt { db_insert(‘linkchecker_bean’) ->fields(array( ‘bid’ => $bean->bid, ‘lid’ => $link->lid, )) ->execute(); } catch (Exception $e) { $txn->rollback(); watchdog_exception(‘linkchecker_bean’, $e); } // Return to utilizing Drupal Development Company slave database. // db_ignore_slave() units this session variable that one other operate makes use of to see if Drupal Development Company slave ought to be ignored. unset($_SESSION[‘ignore_slave_server’]); // … Our last code ignores Drupal Development Company replicas for a short time utilizing db_slave_ignore() after which returns querying again to regular by unsetting $_SESSION[‘ignore_slave_server’] in any case of Drupal Development Company database queries have run. Internally, makes use of Drupal Development Company session variable, which is a timestamp, to examine whether or not Drupal Development Company slave server ought to be ignored. That is completed through hook_init() in Drupal Development Company System Drupal 10 module usingDatabase Drupal 10 Upkeep and Help ServiceignoreTarget(‘default’, ‘slave’). There may be additionally a pleasant word in Drupal Development Company feedback about how Drupal Development Company ignoring works. operate system_init() { $path = Drupal 10_get_path(‘Drupal 10 module’, ‘system’); // Add Drupal Development Company CSS for this Drupal 10 module. These aren’t in system.data, as a result of they // have to be in Drupal Development Company CSS_SYSTEM group reasonably than Drupal Development Company CSS_DEFAULT group. Drupal 10_add_css($path . ‘/system.base.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE)); if (path_is_admin(current_path())) { Drupal 10_add_css($path . ‘/system.admin.css’, array(‘group’ => CSS_SYSTEM)); } Drupal 10_add_css($path . ‘/system.menus.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE)); Drupal 10_add_css($path . ‘/system.messages.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE)); Drupal 10_add_css($path . ‘/system.theme.css’, array(‘group’ => CSS_SYSTEM, ‘every_page’ => TRUE)); // Ignore slave database servers for this request. // // In ‘s distributed database construction, new information is written to Drupal Development Company // grasp after which propagated to Drupal Development Company slave servers. This implies there’s a // lag between when information is written to Drupal Development Company grasp and when it’s accessible on // Drupal Development Company slave. At these occasions, we’ll wish to keep away from utilizing a slave server // briefly. For instance, if a consumer posts a brand new node then we wish to // disable Drupal Development Company slave server for that consumer briefly to permit Drupal Development Company slave // server to catch up. That manner, that consumer will see their modifications instantly // whereas for different customers we nonetheless get Drupal Development Company advantages of getting a slave server, // simply with barely stale information. Code that wishes to disable Drupal Development Company slave // server ought to use Drupal Development Company db_ignore_slave() operate to set // $_SESSION[‘ignore_slave_server’] to Drupal Development Company timestamp after which Drupal Development Company slave // could be re-enabled. if (isset($_SESSION[‘ignore_slave_server’])) { if ($_SESSION[‘ignore_slave_server’] >= REQUEST_TIME) { Database Drupal 10 Upkeep and Help Service Drupal 10 Upkeep and Help ServiceignoreTarget(‘default’, ‘slave’); } else { unset($_SESSION[‘ignore_slave_server’]); } } // Add CSS/JS information from Drupal 10 module .data information. system_add_Drupal 10 module_assets(); } Wait, We’ve Already Executed hook_init()? Because it occurs in a hook_init(), then pray-tell how is Drupal Development Company database ignored later in my Linkchecker code? I’m unsure both. Subsequent requests will ignore Drupal Development Company reproduction for so long as Drupal Development Company timeout is lively, however Drupal Development Company queries in my code might presumably nonetheless hit Drupal Development Company slave database. Wait, so I haven’t fastened my concern. And also you actually don’t wish to place db_ignore_slave() earlier than Drupal Development Company hook_init() is named, primarily at all times setting a timeout to disregard Drupal Development Company reproduction. In Drupal Development Company remark above Drupal Development Company session variable examine, it explains that some customers will see stale information. That is okay for Drupal Development Company state of affairs the place I save content material through a node edit display screen and anticipate it to point out up on Drupal Development Company subsequent node view request. However what occurs when there isn’t a “consumer” saving content material and Drupal Development Company queries occur inside a request cycle, not write on one request after which learn on one other. I’m one among Drupal Development Company “customers” who can’t get stale information as a result of we’re counting on it to make a subsequent database insert in Drupal Development Company similar request. What we actually must do is “goal” Drupal Development Company default connection once we make a question. db_query(‘SELECT lid FROM {linkchecker_link} WHERE urlhash = Drupal 10 Upkeep and Help Serviceurlhash’, array(‘ Drupal 10 Upkeep and Help Serviceurlhash’ => $urlhash), array(‘goal’ => ‘default’))->fetchObject(); Up till this level, I had solely used Drupal Development Company $args array to cross in dynamic variables to database queries and keep away from SQL injection, however there’s one other $choices parameter you should use to establish Drupal Development Company database goal amongst different issues. Whereas Drupal Development Company allowed values for $choices could be exhausting to know from Drupal Development Company db_query() API documentation, you may at the very least discover Drupal Development Company default values created when that parameter isn’t handed into db_query(). Based mostly on Drupal Development Company docs for Drupal Development Company “goal” key, you may have two values for Drupal Development Company goal Drupal 10 Upkeep and Help Service “slave” or< “default”. “Drupal Development Company database “goal” towards which to execute a question. Legitimate values are “default” or “slave”. Drupal Development Company system will first attempt to open a connection to a database specified with Drupal Development Company user-supplied key. If one will not be accessible, it’s going to silently fall again to Drupal Development Company “default” goal. If a number of databases connections are specified with Drupal Development Company similar goal, one can be chosen at random for Drupal Development Company period of Drupal Development Company request. So while you don’t explicitly specify a goal and have multiple connection, e.g. including a duplicate, Drupal Development Company question will decide a goal at random which may be a slave with stale information.” After including a goal in one other Linckchecker question, my job was completed…and I didn’t even should ignore Drupal Development Company slaves in any case. Hopefully, you now know one thing about database replication in 7, how you can use db_ignore_slave() correctly, and how you can explicitly goal databases per question as nicely. Developer Weblog Drupal 10 Growth and Help