By pianory on

I have a module that does updates on every cron job (which is set to 5 minute intervals). This code is like this:

db_set_active('remote');
$results = db_query('SELECT * FROM {mytable}');
//Immediately switch back to the default connection when finished with query
db_set_active('default');

However, I had some negative effects when something timed out - so I learned to minimize the code significantly between the set_actives. Yet, after searching and finding: http://www.sparrowtail.com/dangers-multiple-drupal-database-connections, I am still concerned that this is a negative approach to module development. What if a user chooses the split microsecond the cron job is running to get a page? Or what if the remote database query takes 5 seconds? Problems!

So, it seems like I cannot use the database abstraction layer for this simple query - which isn't a big deal, I'm just interested in any alternate approaches or reasons why this SHOULD be safe. Perhaps a new function that provides a non-global switch would be more appropriate?

Any dialog would be interesting, thanks!

Ryan

⋅ Categories: Drupal 6.x

Comments

pianory’s picture

pianory commented

I noticed that D7 has an options array in the db_query function that seems to fix this issue of having to globally set a new active database. However, I traced the code and have a working (and hopefully also secure since it uses Drupal db code from includes folder) way of accessing the database in a very similar way, for the D6 users (and probably D5 also). Hope this helps -

  global $db_url;
  $remoteConn = db_connect($db_url['remote']);
  $sqlQuery = db_prefix_tables($sqlQuery);
  $sqlQuery = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $sqlQuery);
  $result = mysql_query($sqlQuery, $remoteConn);
jaypan’s picture

jaypan
Location Victoria, BC
commented

DB connections are on a per-user basis, and cron is a user. So even if cron is accessing the other database, that doesn't mean that another user will have that happen.

Contact me to contract me for D7 -> D10/11 migrations.

pianory’s picture

pianory commented

In my first set of tests, the hook_cron for this module ran too long, and the entire website was effected. I believe cron was running as 0 (anonymous) at that time. In other words, I haven't had an experience similar to what you are saying with stove-piped user database sessions. I don't have time currently to run a testbed for this issue since I have a failover measure, but it would be interesting if someone wanted to test that theory!