Last updated May 12, 2013. Created by abramo on May 17, 2008.
Edited by holtzermann17, greggles, davemaxg, wmostrey. Log in to edit this page.
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.
Introduction
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 "my.ini" (Windows) or "my.cnf" (Linux) during the installation of MySQL. In Windows this file is located by default at C:\Program Files\MySQL\MySQL Server X.Y\my.ini. In Linux this file is located at /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. 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! You will also have to reload the MySQL service after making changes to these configuration files.
MyISAM specifications
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-exteral-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 = 32MInnoDB specifications
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 = 180Note: 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. They are called my-huge.ini, my-innodb-heavy-4G.ini, my-large.ini et cetera and they can be found in the default MySQL directory. Keep in mind, for Linux the file extension is .cnf. Browse them and determine which one suits 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.
Comments
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
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
w: http://www.xweb.com.au
e: http://drupal.org/user/1171/contact
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 :)
__________________________
Jonathan Pugh
Founder, CTO
ThinkDrop Consulting, LLC
http://thinkdrop.net
http://twitter.com/thinkdropLLC
Always Open
Another trick, w/ PHP
Another trick w/ PHP
<?php
// your connection
mysql_connect("localhost","root","my_password");
mysql_select_db("my_db");
// convert
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
foreach ($row as $key => $table)
{
mysql_query("ALTER TABLE " . $table . " TYPE=InnoDB;");
echo $key . " => " . $table . " done<br />";
}
}
?>
Meetai - Drupal solutions
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
www.systemseed.com - drupal development. drupal training. drupal support.
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!
Worked for me!!
Worked like a dream. Thank you. I want to have your babies...
Joost
Life saver
Thanks so much, for a NOOB like me who doesnt have a clue and uses a basic cpanel, this was a life saver. Worked great.
Does this hack work for Drupal 6.20? I noticed it is for 5.x
I'm running Drupal 6.20. Does anyone know if this hack works for Drupal 6.20? If so, do I paste the hack above "return $connection;"?
Below is the "function db_connect()" code:
/**
* Initialize a database connection.
*/
function db_connect($url) {
$url = parse_url($url);
// Check if MySQL support is present in PHP
if (!function_exists('mysql_connect')) {
_db_error_page('Unable to use the MySQL database because the MySQL extension for PHP is not installed. Check your
php.inito see how you can enable it.');}
// Decode url-encoded information in the db connection string
$url['user'] = urldecode($url['user']);
// Test if database url has a password.
$url['pass'] = isset($url['pass']) ? urldecode($url['pass']) : '';
$url['host'] = urldecode($url['host']);
$url['path'] = urldecode($url['path']);
// Allow for non-standard MySQL port.
if (isset($url['port'])) {
$url['host'] = $url['host'] .':'. $url['port'];
}
// - TRUE makes mysql_connect() always open a new link, even if
// mysql_connect() was called before with the same parameters.
// This is important if you are using two databases on the same
// server.
// - 2 means CLIENT_FOUND_ROWS: return the number of found
// (matched) rows, not the number of affected rows.
$connection = @mysql_connect($url['host'], $url['user'], $url['pass'], TRUE, 2);
if (!$connection || !mysql_select_db(substr($url['path'], 1))) {
// Show error screen otherwise
_db_error_page(mysql_error());
}
// Force MySQL to use the UTF-8 character set. Also set the collation, if a
// certain one has been set; otherwise, MySQL defaults to 'utf8_general_ci'
// for UTF-8.
if (!empty($GLOBALS['db_collation'])) {
mysql_query('SET NAMES utf8 COLLATE '. $GLOBALS['db_collation'], $connection);
}
else {
mysql_query('SET NAMES utf8', $connection);
}
return $connection;
}
UPDATE: March 21, 2011
Yes, it does work! And yes, I did paste the hack above "return $connection;". No more problems.
Thanks!
cburn
Great fix for Drupal 6.22
I was having the same problem with the Update Status module after I switched my Drupal 6 site over to a different web host. I don't have rights to configure MySQL on this server but the Drupal hack by cog.rusty worked great. I don't like hacking Drupal core, but this seems to be a Drupal issue, so for now it works and I can see when updates are needed. The alternative is never knowing when I have updates so the hack will do for now!
Much thanks
Just wanted to throw my two yen in here-- cog.rusty's fix still works. Got the error while installing the Conference Organizing Distribution (COD). Thanks so much for posting the fix.
you fixed my problem
Thanks cog.rusty! This fixed my problem.
Since it is a hosted site I don't have access to the db config files.
Tried to find a .htaccess setting for it, but did not succeed until now.
Makes upgrading to a new core version easier.
Anyone????
A Schw.
Can this go into a special trouble shooting patch please?
I have also encountered this error on too many cheapo shared hosting sites. Since this seems to be fairly widespread, can't we have a patch like cog.rusty suggests?
Thanks For The Perfect Answer
Thanks Cog Rusty...For The Great Asnwer...Thats Work For Me :D
Thanks to cog.rusty for the
Thanks to cog.rusty for the solution to this fix.
Has anyone figured a solution to this through settings.php?
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 Solution Experts
for 6.x
for Drupal 6.x you may use: http://drupal.org/project/db_tweaks
to increase your max_allowed_packet MySQL limit
Thanks to cog.rusty's solution and disable module Update status
Thanks cog.rusty, your solution of changing wait_timeout in database.mysql.inc and database.mysqli.inc worked for me.
I am using Drupal version 6.14.
also thanks to the comment to disable module Update status. This greatly reduce the time of loading the site.
cog.rusty, thanx you a lot.
cog.rusty, thanx you a lot. It was the only way that worked on my site (i tried to change php.ini, htaccess and nothing).
drupal 6.16, a lot of modules.
btw: i didn't switch off update module, it very handy to me. instead i added Update status advanced settings module to set
an appropriate timings.
Fix for error "Plugin 'InnoDB' registration as a STORAGE ENGINE"
I'm using WAMPServer on Windows 7 x64 with MySQL v5.1.36
After making the recommended changes (specifically the InnoDB lines), MySQL failed to restart. So I checked the logs and saw these messages:
100405 [ERROR] Plugin 'InnoDB' init function returned error.100405 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
On searching for a fix, I came across this thread. I think the problem with mine was that it changed from 5MB to 10MB. The solution was to go into MySQL's Data directory and delete all log files that have a name like "ib_logfile#" (where # is incremental). The next time that MySQL starts, it will jump into an auto-recovery routine and recreate the log files.
~ Digital Cowboy Extraordinaire ~
wait_timeout was the culprit for me
FWIW, in my case (on a VPS, with all parameters available for tweaking), the issue causing the problem for me was the wait_timeout variable in my.cnf.
For whatever reason this had been set to 10, and setting it to 100 resolved the problem.
worked for me; YMMV...
Had the same issue I
Had the same issue
I increased :
max_allowed_packet = 200M
and it helped me
windows 7 localhost using latest xamp
These mysqld settings worked for me in the latest xamp setup on a windows 7 localhost. The setup in the default my.ini were designed for systems with low memory.
protoplasm
This error results from using drupal to protect file access
In my case, this error occurs sometimes when I use drupal to prevent anonymous users from accessing some large (2 MB) PDF or Flash files.
I have a website with pdf and flash files that only authenticated users should access, so I have the following line in my .htaccess file.
RewriteRule ^mediafiles/(.*\.(pdf|swf))$ /system/files/mediafiles/$1 [L,R=301]
This prevents anonymous users from viewing the content. But, I get about 6 of these dreaded "Warning: MySQL server has gone away" messages per day and they are always when someone is accessing a large (2 MB and over) pdf or swf files. Many users can access those files without any problems, so it is only under certain conditions (which I have yet to determine) that this error message occurs.
I'm a bit hesitant to increase the mysql resources for this when all I need is to ensure that only authenticated users can access the media files. it also seems like an overkill to write my custom module just to check if the requester has a valid session and belongs to a valid role type. I would think that I am not the first to encounter this issue???
Sergio
Hiya...sorry to perform
Hiya...sorry to perform thread necromancy, but I just wanted to say that:
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');
worked for me under the latest version of Drupal.
seems to work here too
(tentatively) Fixed on drupal 6.19
With no access to my.ini with my web host i tried:
In includes/database.mysql.inc, at the end of function db_connect(), in the line below the "SET NAMES" line, add:
mysql_query('SET SESSION wait_timeout = 60', $connection);
In includes/database.mysqli.inc, at the end of function db_connect(), in the line below the "SET NAMES" line, add:
mysqli_query($connection, 'SET SESSION wait_timeout = 60');
All good so far
life saver - attention for details
works great!
But you need to pay attention where to past:
In includes/database.mysql.inc, (mysql.inc not mysqli.inc) at the end of function db_connect(url here), in the line below the "SET NAMES" line (actually there are two places were "SET NAMES" occured, i added the following coe after both of them) lines , add:
mysql_query('SET SESSION wait_timeout = 60', $connection);
In includes/database.mysqli.inc, at the end of function db_connect(url here), in the line below the "SET NAMES" line, add:
mysqli_query($connection, 'SET SESSION wait_timeout = 60');
I copied and pasted the code
I copied and pasted the code directly into my.ini, restarted mysql service, and it worked!! <3 Thank you!! :D
It also works.....
To solve the above issue, you can perform the following steps:
If the connection is closed, then you should reestablish the connection and see if it works well.
Run the "OPTIMIZE TABLE " command. If you get the "ERROR 1030 (HY000): Got error 28 from storage engine" message, then this would mean that there is no space in the drive to get the query result. Then, to repair the database by optimizing it, you need to perform the following steps:
Add the following line in the "mysqld" section in my.cnf:
"innodb_force_recovery=4"
Stop "mysqld" and restore data backup to "mysqld" data folder.
Restart "mysqld" and perform a thorough check on your tables.
Try to execute a large query.
Optimize all the database tables.
For details read more from here:
http://www.articlesbase.com/data-recovery-articles/repairing-corrupt-mys...
some additional things to do...
Aside from what abramo said and it's absolutely right, you need to consider something else. This problem sometimes first appears when you try to install couple of modules toghether and you receive a server time out message. you may try to continue but after that you get this let's say not fixable problem even if you increase those numbers in my.ini
My suggestion is that you increase those numbers and start all over again from a back up of your dadabase that you took before installing so many modules toghether. uninstalling one or 2 modules may resolve the problem temporarily but you'll receive this error message over and over again so do not waste you time, just import a good back up and with this knowledge start over.
Plus, you can adjust those number in your own developement machine (LOCALHOST). If your website is live, you need to contact the host to address the issueand no need to mention that either way you need to import a good back up and start over because once you receive this message, it's like a cancer!
max_allowed_packet = 64M fixed it
I was getting this error on multiple pages referencing multiple modules so I struggled to pinpoint the issue. Disabling the Update Status module didn't do anything. When I moved the database to my local MAMP server it was fine, but on the production WAMP server using Zend, the error came up all over the place, even though the MySQL configuration was exactly the same.
Adding "max_allowed_packet = 64M" to the my.ini file and restarting MySQL service did the trick. I'm still not sure why a 1M (default) max_allowed_packet on the local server works but breaks on the production server, but I'm glad to have a solution!
Good solution
I solved my problem (local installation) by your solution. Thanks.
Using MAMP PRO the ini file of MySql is easily found.
Excellent
The "max_allowed_packet = 64M" fixed my issue. I was getting an error when I would click "Edit" for a content type. I had thought it was related to automatic nodetitle because if I disabled that module then I could edit fine, re-enable and error would come back. Turns out it was memory related!
Mamp: "MySQL server has gone away" du to cache_menu / cache_view
As for me, NO solution work with the free version of Mamp, EXCEPT the solution given on this post on the forum of mamp:
PS: for me, I had this error only when importing values of cache_menu and/or cache_views on Drupal 7
http://www.stephanebouillet.com
http://www.biolodidje.com
Abramo, Thanks very much for
Abramo,
Thanks very much for the posting. This saved the day. Thank you!
Matt
PHP Limit and mySQL max packet size
I've been through a lot of shared hosting platforms and they all seem to have some sort of limiting factor. Changing to "max_allowed_packet = 64M" in the ini file worked for me. On some sites you have to make sure you are running Innodb tables vs myisam. This has also saved me from some trouble.
JoshAust.in
Dont know what way worked
Dont know what way worked but well..I finally got it working after i restarted MySQL!
Thanks a bunch all of you loyal drupal-users!
Added "max_allowed_packet =
Added "max_allowed_packet = 64M" and it worked.
Thanks!
this info made my day
thx
[solved] best for me (server has gone away)
None of these solutions worked for me in any consistent way. I have hit this error several times. What worked for me is to place the 'max_allowed_packet=64' as an argument in the sh script that mysql launches mysql by (this file can also be run directly from the command line). It is /Applications/MAMP/bin/startMysql.sh. Place '--max_allowed_packet=64' before the ending '&'. This is the only consistent solution I have found. Remember to restart MAMP.
Thanks
Worked for me... obviously this is a solution for a local environment.
Are there any implications for when you push the site live to your host? This is the first time I've seen this error.
Warning: MySQL server has gone away
I got this kind of error when dealing with large data sets with blob fields. when i increased the max_packet_allowed to 512M in my.cnf, and it worked fine.
Thanks
Sirish Ayyagari
Drupal Architect
Is this need to be changed in
Is this need to be changed in the PHP.ini file? I don't see any
max_packet_allowedentry in it, where should it be added?You're editing the MYSQL file
You're editing the MYSQL file (if you use MYSQL), not the PHP.ini file.
In Windows: C:\Program Files\MySQL\MySQL Server X.Y\my.ini
In Linux: /etc/my.cnf" (to set global options) or "/usr/local/var/mysql-data-dir/my.cnf"
I believe you can add it anywhere in the file. But if it already exists in your file, all you have to do is change the number to be higher.
CRON on a shared server - connect time out
Got this message after installing linkchecker.
CRON runs longer than 20 secs to start with (lots of links to check) and causes a time-out, the MySQL connection is closed. Hence the warning;
I solved it by editing PHP.INI with
mysqli.reconnect = on
It's not the prettiest solution but it worked for me.
wily variable_set stuffed variables and broke my site
Here's an esoteric take on the MYSQL Server has gone away:
A particular entry in my variables table was stuffed with some gnarly stuff remnant from debugging. "I tried all the methods above" until mysql.allow_reconnect = On finally got me over the hump. Left with a screen chock-full of pink drupal pepto spew, I found that by deleting this one custom variable out of the variables table, voila, all my life was made whole again!
In my case, the variable was getting bloated with XML packets received from outside, and I feel like one was interrupted mid-write somehow.
(Comment spam has gotten so good, it's got me questioning my humanity!)
--
..happiness is point and click..
http://www.bronius.com
PDO BUFFER
There are many issues to check here, but one I haven't heard yet is increasing the size of the PDO max buffer size. See #1302240-4: Enable admin_menu with toolbar give MySQL server has gone away: for details.
--
Tom/* Ogden