May 10 2010
May 10

In environments where there are many databases running on the same machine (ex. shared hosting), or in high traffic environments (ex. enterprise sites) it is a common problem that unterminated connections to the database linger around indefinitely until MySQL starts spitting out the "Too many connections" error. The fix for this is decrease the wait_timeout from the default 8hrs to something more in the range of 1-3 minutes. Make this change in your my.cnf file. This means that the MySQL server will terminate any connections that have been sitting around doing nothing for 1-3 minutes.

But this can lead to problems on the other side where now MySQL is terminating connections that are just idle, but will be called on to do something. This results in the "MySQL has gone away" error. This problem is unlikely to happen with stock Drupal, but is much more frequent with CiviCRM (or any other time where you connect to more than one database). The issue being that sometimes an intensive process will happen in one database, then action needs to return to the other database, but oops MySQL has terminated that connection. This is most likely to happen on anything that takes a long time like cron, contact imports, deduping, etc.

There's a little known trick with changing wait_timeout on the fly. You can do this because wait_timeout is both a global and per-session variable. Meaning each connection uses the global wait_timeout value, but can be changed at any time affecting only the current connection. You can use this little function to do this:

<?php
/**
* Increase the MySQL wait_timeout.
*
* Use this if you are running into "MySQL has gone away" errors.  These can happen especially
* during cron and anything else that takes more than 90 seconds.
*/
function my_module_wait_timeout() {
  global
$db_type, $db_url;
 
 
watchdog('my_module', 'Increasing MySQL wait timeout.', array(), WATCHDOG_INFO);
  if (
is_array($db_url)) {
   
$current_db = db_set_active();
    foreach (
$db_url as $db => $connection_string) {
     
db_set_active($db);
     
db_query('SET SESSION wait_timeout = 900');
    }
    if (
$current_db) {
     
db_set_active($current_db);
    }
  }
  else {
   
db_query('SET SESSION wait_timeout = 900');
  }
 
  if (
module_exists('civicrm')) {
   
civicrm_initialize();
    require_once(
'CRM/Core/DAO.php');
   
CRM_Core_DAO::executeQuery('SET SESSION wait_timeout = 900', CRM_Core_DAO::$_nullArray);
  }

}

?>

Then call this function before anything that might take a long time begins.

There's also an issue in the CiviCRM issue queue to make CiviCRM do this before any of it's long internal operations.

About Drupal Sun

Drupal Sun is an Evolving Web project. It allows you to:

  • Do full-text search on all the articles in Drupal Planet (thanks to Apache Solr)
  • Facet based on tags, author, or feed
  • Flip through articles quickly (with j/k or arrow keys) to find what you're interested in
  • View the entire article text inline, or in the context of the site where it was created

See the blog post at Evolving Web

Evolving Web