Last updated 8 February 2017. Created on 17 May 2008.
Edited by ann b, saurabh.dhariwal, chapabu, batigolix. 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.cnf" (Linux) or "my.ini" (Windows) during the installation of MySQL.

  • 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.
  • In Windows this file is located by default at C:\Program Files\MySQL\MySQL Server X.Y\my.ini.

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-external-locking
key_buffer = 384M
max_allowed_packet = 64M
table_open_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 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 = 180

Important: table_cache was renamed to table_open_cache in MySQL 5.1.3 (Changes in MySQL 5.1.3). If you are running an older version of MySQL then you should replace table_open_cache with table_cache.

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

Looking for support? Visit the Drupal.org forums, or join #drupal-support in IRC.

Comments

matt westgate’s picture

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

Jupiter’s picture

This was helpful to configure my development server on Windows :

http://dev.mysql.com/doc/refman/5.1/en/windows-create-option-file.html

matt westgate’s picture

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

Chris Herberte’s picture

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.org

lynx 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

Jon Pugh’s picture

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.sql

this 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 :)

__________________________
Jon Pugh
President
OpenDevShop Inc
http://opendevshop.com
http://twitter.com/jonpugh

ari-meetai’s picture

Another trick w/ 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
"; } }
cog.rusty’s picture

I have seen the "MySQL server has gone away" error on a couple of cheap shared hosting accounts where they had set wait_timeout=15 to save connections. By the way, MySQL's default is wait_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.

mrfelton’s picture

This should be a configurable setting through settings.php

--
Tom
www.systemseed.com - drupal development. drupal training. drupal support.

cog.rusty’s picture

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.

kenorb’s picture

Try this:
http://drupal.org/project/db_tweaks

It will allow you to change those settings.

kenorb’s picture

for Drupal 6.x you may use: http://drupal.org/project/db_tweaks
to increase your max_allowed_packet MySQL limit

hargobind’s picture

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.

sgarcia.sd@gmail.com’s picture

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???

addisionphilip’s picture

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

OLD ACCOUNT USE ID 169175 INSTEAD’s picture

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!

bleeuwen’s picture

I solved my problem (local installation) by your solution. Thanks.

Using MAMP PRO the ini file of MySql is easily found.

kevroc’s picture

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!

remedact’s picture

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

joeshock’s picture

Thank you!

R-Os’s picture

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!

nomad-drupal’s picture

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.

texas-bronius’s picture

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!)

--
http://drupaltees.com
80s themed Drupal T-Shirts

tomogden’s picture

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

Clint Eagar’s picture

Whenever I add the Innodb configs from above to my.cnf MySQL won't start :(

I'm running on a VPS with 1GB RAM.

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

This is the MySQL error log:

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 10485760 bytes!
130902 14:09:07 [ERROR] Plugin 'InnoDB' init function returned error.
130902 14:09:07 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130902 14:09:07 [ERROR] Unknown/unsupported storage engine: InnoDB
130902 14:09:07 [ERROR] Aborting

Any ideas?

ranelpadon’s picture

You must rename the log files so that it will create new ones, by default, ib_logfile0 and ib_logfile1 are both set to 5MB, and setting them to 10MB (via "innodb_log_file_size") will throw errors.

For example, if you run XAMPP for Linux, you must run:
$ mv /opt/lampp/var/mysql/ib_logfile0 ib_logfile0_bak
$ mv /opt/lampp/var/mysql/ib_logfile1 ib_logfile1_bak

So that it will move/rename the current log files and will make way for new ones.
Restart the MySQL daemon/service, it will then create new log files based on what you set in the "innodb_log_file_size".
If you don't use XAMPP for Linux, just find the corresponding log files, and rename them.

charginghawk’s picture

Last time I ran into this, it was using vagrant + VirtualBox, and the fix was to give my VM more RAM through VirtualBox's control panel.

malcomio’s picture

When I edited the my.cnf in my DrupalVM as suggested, and ran sudo service mysql restart, the MySQL service failed to restart for some reason. Not sure why, but after reverting some of the values to the defaults, and increasing others, I got it working again.

For what it's worth, I've put the configuration into a Gist: https://gist.github.com/malcomio/40d83a37a26c9ea28c9cf9f189e3b6be

code-drupal’s picture

Increase max allowed packet and query cache size to a higher value.