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 "my.cnf" (Linux) or "my.ini" (Windows) during the installation of MySQL.
- In Linux this file is located at
/etc/my.cnfto set global options, or
/usr/local/var/mysql-data-dir/my.cnfto 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
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
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-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.