MySQL: "Warning: MySQL server has gone away"

[源] = [ http://drupal.org/node/259580 ]

MySQL: "Warning: MySQL server has gone away"

• Drupal version: Drupal 5.x, Drupal 6.x, Drupal 7.x
• Audience: Developers and coders, Site administrators
• Last modified: August 15, 2011

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.