MySql : " Warning: MySQL server has gone away " - Tune MySql to resolve this problem
by abramo
" Warning: MySQL server has gone away "
This dreaded MySql error and subsequent Warning is due, except in rare cases, to a lack of resources available to MySql, such as required for the operation of your Drupal installation. Allowing the necessary resources to MySql resolves this issue most of the time. Also, it is easy to resolve, if you know how to proceed.
Here is a step by step guide, equally valid for your Linux server as well as any local Windows MySql installation you may be using as a trial installation along with your local Drupal installation.
MySql comes with a default configuration of the resources it is going to use, specified in the "my.cnf" file (Linux) or "my.ini" file (Windows) during installation of MySql. This file is located by default at "C:\Program Files\MySQL\MySQL Server X.Y\my.ini" in Windows - or in Linux "/etc/my.cnf" (to set global options) or "/usr/local/var/mysql-data-dir/my.cnf" (to set server-specific options).
Resources allowed by the default configuration are normally insufficient to run a resource-intensive application (but on the safe side just in case the server is not powerful enough). You must modify the following resource specifications if they are available in your original configuration file, or add them to the configuration file if they are not already specified (because some are not present by default) :
(Important: remember to keep backup files *before* you do anything !!)
GENERAL SPECIFICATIONS:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 64M
table_cache = 4096
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
INNODB SPECIFIC:
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 180
Note: It is assumed here that you are using the InnoDB database tables, as Drupal is a resource intensive application. If you are not using the InnoDB database tables try to change this, in view of the fact that you are getting the "Warning: MySQL server has gone away" - apparently meaning that your setup is resource intensive. Convert MyISAM Tables to InnoDB .
Where do these specifications come from?
MySql provides sample configuration files within its package (called in Windows "my-huge.ini" , "my-innodb-heavy-4G.ini" , "my-large.ini" , etc - for Linux "*.cnf"). They are normally to be found in the default MySql directory. Browse them and determine by yourself what suits you (and your system) best. The above recommendations, provided here as a starter guideline, will do the trick in most Drupal cases and average modern machine specifications. Remember, anything you modify or add must reside within your "my.cnf" / "my.ini" file for the system to find and use. Also, remember to keep backup files *before* you do anything.
Hosting company limitations
I began seeing the "MySQL server has gone away query" error only after moving my sites to a new hosting company. HostGator told me that it has "a MySQL process killer in place that will kill any process that takes longer than 15 seconds to complete."
In addition to the excellent suggestions above, be aware that some hosting companies also have artificial limits on MySQL process time.
Killing process works
I use godaddy to host and ran into the same issue. Using their mysql admin capability looked at the processes to find one with a kill option. Did not have much of a clue as to the impact of killing it but when I tried that everything worked fine. Not sure if it was coincidence or killing that process worked.
Be careful with your hosting provider...
FYI
I have my drupal site hosted on HostICan (www.hostican.com) hosting provider (aka HIC). HIC Team refused to change the parameter of mysql 'max_allowed_package' to increase the buffer size between Drupal and MySQL in a shared environment. They forced me to upgrade to a Virtual Private Server hosting package to allow this config modification.
To let you figure out my drupal site resources usage and help you decide which hosting package you will need to hire when using Drupal on HostICan read the following:
- My site has less than 100 registered users.
- My site has never had more than 15 concurrent users.
- My site AWStats say that from June the 1st to June 26th we've had an average of 141 visits per day with a bandwidth consumption of 160MB per day.
Clearly, in my honest opinion, this is a real little Drupal installation. Therefore we can conclude that HostICan is not prepared to host Drupal sites on their Base Hosting package, be careful.
HostICan has a good system technologies environment, but be carefull if you are trying to mount a "real" drupal site on their hosting. If you hire a Base hosting, you will only be able to "play" with drupal...
Regards
P.s: This post is not to make any preasure on HostICan team nor being dissuasive with new HIC users. My only intention is to inform all of you about what you will need to hire if you plan to build a Drupal site on HIC.
I think most web host
I think most web host providers don not allow any customizations with the MySQL configuration at shared hosting servers, siteground.com one of them (http://forum.siteground.com/showthread.php?t=3181).
my experience
I too am a (victim is not the right word, since I know and understand memory limits must be imposed on shared web hostings) ... shall we say, "affected" by this issue?. I'll post my experience in case it helps anyone.
My problem came when I started using the module Backup and Migrate to make a DB backup. It gave this error after hours and hours of executing cron, and this brought the site to a halt (good thing it was a sandbox). Anyway, I could *NOT* get into the site to fix the problem, which I assumed would be restored by deleting backup and migrate module and resorting to manual backups for the time being. Without access to /admin, though, I could not see how to do this at first.
After over 24 hours of having the site being non responsive, I got in via FTP, deleted the backup_migrate dir from /sites/all/modules and all files from /sites/default/files/backup_migrate. After about 30 minutes, my site started responding again. At this point, I re-uploaded backup_migrate module, so that I could disable and uninstall it properly, and now I have a fully working site again. I had to backup the database via phpmyadmin though.
Upgrade Bug
I've also seen this bug crop up due to incomplete upgrades from MySQL 4 to MySQL 5. Make sure you have run the 'mysql_upgrade' command as part of the upgrade process:
http://dev.mysql.com/doc/refman/5.0/en/mysql-upgrade.html
***
www.lullabot.com - making open source easy
Some more details about where to find the my.ini file
This was helpful to configure my development server on Windows :
http://dev.mysql.com/doc/refman/5.1/en/windows-create-option-file.html
strange problem
i have no my.ini file in mysql installation dir.
I am using XAMPP. Where should i look in?
my or my.cnf
If you're using Win XP/Vista OS, take a look at C:\xampp\mysql\bin\my.cnf
Most probably that you'll see a filename "my" without additional extension. Open it with your text editor.
--
www.jazzit.hr
cPanel Update Bug
If you're seeing this error while running a site behind SSL and are using cPanel, you may need to run the script at /scripts/ssl_crt_status resync your SSL certs. It's an edge case, but good to know nonetheless.
***
www.lullabot.com - making open source easy
My Pro Drupal Developmen
Site5, a popular and cheap
Site5, a popular and cheap hosting provider has some pedantic firewall settings and http://updates.drupal.org/ gets blocked, causing the above (OP's) error message a bunch of times. To test, ssh to your Site5 server:
[you@server ~]$ lynx http://updates.drupal.orglynx to http://updates.drupal.org/ will hang for many minutes if the above is the case, the solution is to open a support ticket and suggest white-listing http://updates.drupal.org IP (subject to change??) I'm told this may reoccur when firewall is updated, requiring another ticket.
This was a ball-tearer to find and I spent the best part of a day finding it.
I hope this helps
OMG thank you so much
Chris I have had this problem over and over with my sites and never knew what it was. I just contacted site5. I really hope that's it. It's cost me hours and hours.
I'd love to know how you figured that out.
thanks so much for posting it
Phil
Easy convert to InnoDB script
Hey all,
been wrestling with this myself, on Drupal 6, repeatedly, and on dev sites with only 1 user.
There's no way to update all tables in SQL, so here's a nice shell script that will generate the MySQL needed to update all your drupal tables to INNODB, using drush. you could also use mysql command itself, I just like drush :)
at SSH terminal:
./drush -l http://example.com sql query "SHOW TABLES;" | awk '{print "ALTER TABLE "$1" TYPE=InnoDB;"}' > table_update.sqlthis will save a file called table_update.sql containing an ALTER TABLE statement for each table found in your database. you can then run the sql on the tables, after backing up, like so:
./drush -l http://example.com sql dump > backup.sql
./drush -l http://example.com sql query < table_update.sql
I thought this might be helpful to put this in this page itself, I just wasn't sure if it was appropriate :)
Disable Update Status Module
The way out for this labyrinth for me has been disabling the (helpful btw) Update Status Module.
I ask first to my hosting to enable max_allowed_packet=24M
We've tried also to run mysqli.reconnect = On;
But without good results.
By the way, I'm quite happy with the technical support of my hosting service allowing that fine tuning.
Then I saw this comment: http://acquia.com/node/45684#comments
"Most often when there is slowness related to Update Status it has to do with the server blocking outbound HTTP requests."
It works fine now !! Just Disable the module
My workaround: Drupal 5.14 - Hosted in http://www.sync.es
Update Status 5.x-2.3
re: Disable Update Status Module WORKS!
I'm on Site5 -- rather than the hassle of support tickets I disabled Update Status -- thank you pedrotribu!
I love Update Status, but ...
I have seen the "MySQL server
I have seen the "MySQL server has gone away" error on a couple of cheap shared hosting accounts where they had set
wait_timeout=15to save connections. By the way, MySQL's default iswait_timeout=28800. 15 is a bit too drastic.Since I had no access to MySQL's settings, I had to hack Drupal core to fix it.
In includes/database.mysql.inc, at the end of function db_connect(), under the "SET NAMES" line:
mysql_query('SET SESSION wait_timeout = 60', $connection);In includes/database.mysqli.inc, at the end of function db_connect(), under the "SET NAMES" line:
mysqli_query($connection, 'SET SESSION wait_timeout = 60');I guess someone with more PHP experience that me could suggest a patch to check if wait_timeout is too low and fix it in some economic way.
This doesn't work for Drupal
This doesn't work for Drupal 6
update: I take it back it's working, no more mysql away messages.
This should be a configurable
This should be a configurable setting through settings.php
--
Tom
kirkdesigns.co.uk - web design and development
Yes, that would be good if
Yes, that would be good if more people can verify that this setting works. Chime-in in the issue in http://drupal.org/node/227445 if it does or if it does not.
db_tweaks
Try this:
http://drupal.org/project/db_tweaks
It will allow you to change those settings.
ok, hack works.
cog, thanks.
This hack also worked for me for Drupal 6.12 on a shared hosted environment.
My hosted server has wait_timeout=30sec, max_allowed_packet=16mb and these "cannot be changed".
The hosted says theses should be adequate, which I'd agree with.
This "gone away" issue only appeared on my testsite and livesite (just went live) a few days ago.
Cause?
- I upgraded some modules?
- I deactived some modules and removed module-directories from filesystem (without doing uninstall ... i'm learning)?
- My hoster changed something, but I always suspect myself first..
Maybe available status should be redesigned to not take so "long" so as to avoid the potential for timeout if a good few modules are installed.
Cron was also failing probably due to available status timeout (updates.drupal.org was not blocked).
Fixes upload module breakage
I can confirm that it fixes the issue for me on Drupal 6.12 with a shared hosting. Previously, I had to disable the update module or all admin pages were unreachable because of the "MySQL server has gone away" error.
Editing the 2 database files solved my problem, and I was able to enable the update module again, thanks
This fix appears to no longer
This fix appears to no longer be working, I'm using the latest Drupal 6 and getting the mysql away messages again, a whole bunch of them. The only thing that seems to fix it permanently is to disable the update module.
Works like a charm!
I'd been facing this problem for a long time now and was searching for a solution for the same. Finally came across cog.rusty's hack and its working like a charm.
thanks a lot mate :)
Works great with Drupal 6.14
I have a drupal site at x10 hosting. They recently increased their php_memory_limit to 64M, but as with many free hosts, the wait_timeout is only 15 secondes. This hack saved my day and I can access the status report!
Thank you!
Works! ... after restarting the service
On my localhost server the above settings did the trick ,,, remember to restart the MySQL service though after changing and saving the new settings to see any effect.
Benjamin R.
Plus flashing - might help a bit as well
Plus flashing - might help a bit as well...
especially if you use modules like Administration Menu.
I made my MySQL go away when upgrading Administration Menu - when I got back going thanks to this thread - I flashed the caches and the Administration Menu cache and got back to the site I used to see.
I am using WampServer 2.0 -
Thanks for all the advice here and all over Drupla.org - ....
Flashing what is flashing ?
Flashing what is flashing ? Or did you mean Flushing :))
not on Vista x64
i am just moving devel system to Vista and x64 version of VAMP - one of my Drupal sites fails with the "server went away error".
Adding the mysqld section from above cures this BUT adding the innodb section at the end of my my.ini and my mysql server fails to restart.
Peter Lindstrom
LiquidCMS - Content Management Solution Experts
Looks like it's works on 5.18
Looks like it's works on 5.18 as well!
Big thanks to you !!!!
www.ookoodoo.com > When drupal became a wishList
cog.rusty - your code saved
cog.rusty - your code saved my day(s) , big thanks to you, man !
I believe somebody should inform the developers of the Update module to incorporate some settings like this:
- normal mode
or
- shared hosting mode
And when you switch it to shared hosting mode - it just makes no more than 5 checks at a time, then the next 5 and etc, thus the update checks not going over the the wait_timeout limits.
for 6.x
for Drupal 6.x you may use: http://drupal.org/project/db_tweaks
to increase your max_allowed_packet MySQL limit
That module didn't really
That module didn't really make the messages go away, still have them.
This module helped me solve
This module helped me solve this problem, i recommend it.
what module?
which module would that be?
http://www.my-kart.org/
the one i replied on :-)
the one i replied on :-) db_tweaks
thanks.
oh. ok. thanks. :)
am not used to following threaded comments. more used to flat comments. ;)
http://www.my-kart.org/
Yeah
but hosting provider can't give me special config, and I have some query that is killed after 30 seconds...
Anw warning with "gone away"...
What is the difference that all works fine on my localhost but not on online server...?
hello drupal world!