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
Comments
Possible solution
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);
DB connections are on a
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.
Interested in hiring us? jaypan.com
Cron user
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!