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:
$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.