Monday, November 9, 2009

drupal admin mysql has gone away!

Its such a hectic day.
I tried to clear the cache and i'm getting bunch of errors from mysql has gone away!
It turn out to be some very long query to update cache_update table and caused it to started to lost connection to db.. arg! I added a static variable to count the # of queries to database,, wow! 250+ queries! the front page itself about 110+queries!....

my here is my quick fix to the problem:
edit: drupal/includes/database.mysql.inc
function: _db_query
line #111, before mysql_query line.
(updated 10th nov: if ping is placed after mysql_query, db_effected_rows will always return -1, which causes lots of other issues)

//****BEGIN ATTEMPT TO RECONNECT ON ERROR
try {
if (! mysql_ping($active_db))
{
throw new Exception("db closed!");
}
}
catch (Exception $e)
{
//attempt to reconnect
global $db_url;
$urlname = "default";
if (is_array($db_url)) {
$connect_url = array_key_exists($urlname, $db_url) ? $db_url[$urlname] : $db_url['default'];
}
else {
$connect_url = $db_url;
}

$active_db = db_connect($connect_url);
}
//****END ATTEMPT TO RECONNECT ON ERROR


And these are my modules i've installed, while some are my custom modules:
/var/www/mydrupal/sites/all/modules/advanced_help
/var/www/mydrupal/sites/all/modules/aes
/var/www/mydrupal/sites/all/modules/apisetting
/var/www/mydrupal/sites/all/modules/apisettinglist
/var/www/mydrupal/sites/all/modules/apiutils
/var/www/mydrupal/sites/all/modules/backup_migrate
/var/www/mydrupal/sites/all/modules/birthdays
/var/www/mydrupal/sites/all/modules/calendar
/var/www/mydrupal/sites/all/modules/captcha
/var/www/mydrupal/sites/all/modules/casetracker
/var/www/mydrupal/sites/all/modules/cck
/var/www/mydrupal/sites/all/modules/computed_field
/var/www/mydrupal/sites/all/modules/content_access
/var/www/mydrupal/sites/all/modules/crm_account
/var/www/mydrupal/sites/all/modules/customcasetracker
/var/www/mydrupal/sites/all/modules/customintranet
/var/www/mydrupal/sites/all/modules/date
/var/www/mydrupal/sites/all/modules/filefield
/var/www/mydrupal/sites/all/modules/flexifilter
/var/www/mydrupal/sites/all/modules/hellomodule
/var/www/mydrupal/sites/all/modules/i18n
/var/www/mydrupal/sites/all/modules/imageapi
/var/www/mydrupal/sites/all/modules/imagecache
/var/www/mydrupal/sites/all/modules/imagefield
/var/www/mydrupal/sites/all/modules/imce
/var/www/mydrupal/sites/all/modules/imce_wysiwyg
/var/www/mydrupal/sites/all/modules/jquery_dataTables
/var/www/mydrupal/sites/all/modules/languageicons
/var/www/mydrupal/sites/all/modules/mail_api
/var/www/mydrupal/sites/all/modules/messaging
/var/www/mydrupal/sites/all/modules/mimedetect
/var/www/mydrupal/sites/all/modules/mimemail
/var/www/mydrupal/sites/all/modules/node_gallery
/var/www/mydrupal/sites/all/modules/node_privacy_byrole
/var/www/mydrupal/sites/all/modules/notifications
/var/www/mydrupal/sites/all/modules/notify
/var/www/mydrupal/sites/all/modules/og
/var/www/mydrupal/sites/all/modules/pear
/var/www/mydrupal/sites/all/modules/print
/var/www/mydrupal/sites/all/modules/rdf
/var/www/mydrupal/sites/all/modules/realname
/var/www/mydrupal/sites/all/modules/schema
/var/www/mydrupal/sites/all/modules/simplemenu
/var/www/mydrupal/sites/all/modules/simplenews
/var/www/mydrupal/sites/all/modules/subscriptions
/var/www/mydrupal/sites/all/modules/swftools
/var/www/mydrupal/sites/all/modules/thickbox
/var/www/mydrupal/sites/all/modules/token
/var/www/mydrupal/sites/all/modules/translation_overview
/var/www/mydrupal/sites/all/modules/transliteration
/var/www/mydrupal/sites/all/modules/ubercart
/var/www/mydrupal/sites/all/modules/video
/var/www/mydrupal/sites/all/modules/views
/var/www/mydrupal/sites/all/modules/views_customfield
/var/www/mydrupal/sites/all/modules/wysiwyg

2 comments:

Nico said...

This stopped for me when I increased the MySQL packet size in mysql/bin/my.cnf considerably:

max_allowed_packet = 256M

James said...

Thanks a lot Nico :)
Hm... i dont have control over the hosting..