The dangers of multiple Drupal database connections
Posted on: 20 October 2009
Our latest venture, www.analyticsseo.com, is a pretty complex app for a Drupal site. There are two parts to the system:
- a back-end application, which crawls the web, analyses sites and competitors, and does all kinds of clever stuff
- a front-end application, which summarises this data, generates tasks, and manages workloads
Fortunately, Drupal makes it really easy to work with multiple databases. Simply add two database connection strings in the settings.php:
$db_url['default'] = 'mysqli://user1:password1@server1/database1'; $db_url['backend'] = 'mysqli://user2:password2@server2/database2';
Then in the code:
db_set_active('backend');
// do stuff in backend database
db_set_active();
// do stuff in front-end database
and all is sweet.
Until you do something where it isn't sweet without realising it.
It's really important to keep the code between the db_set_active calls really targetted on database interaction - don't do anything else between those calls! If you call other Drupal functions, there's a good chance that they will do some stuff based on the assumption that they are connection to the default database, when they are not. This is particularly true of theme functions, which can get terribly confused.
I made the mistake of calling a theme function in my db_set_active block like this:
db_set_active('backend');
// get data from backend database
// put data into form elements
$form['site_pager'] = array(
'#type' => 'markup',
'#value' => theme('pager', null, $results_per_page, 1),
);
db_set_active();
and ended up getting a hugely unhelpful
Fatal error: Call to undefined function zen_menu_item_link() in /var/www/seo/sites/all/modules/drupal-contrib/dhtml_menu/dhtml_menu.module on line 79
Looking at the code in dhtml_menu.module didn't help much, nor did dumping a debug_backtrace() in the function, and nor did Google. Fortunately I had a very similar function elsewhere which was working, so by comparing the two I was able to spot the difference and straighten things out.
So the moral is, while using multiple databases in Drupal is conceptually really easy, always remember:
- set your secondary database active
- get your data
- get the hell out of that secondary database as fast as possible