Tuesday, September 29, 2009

Warning: MySQL server has gone away

SkyHi @ Tuesday, September 29, 2009
If you get the following error message:

*Warning*: MySQL server has gone away

that means your Php or Perl /cgi powered application has not passed a query for a while. The connection to MySQL times out. To start up a new and simple query session, select a table within the database of that application via PhpMyAdmin.

If that does not help, you need to tweak MySQL conf file. Using your favorite Linux text editor such as: vi or pico,

* vi /etc/my.cnf

Set the following values for these two variables:

connect_timeout=120
wait_timeout=1800

If these values do not help, you can increase them.

Then restart MySQL. This command will restart MySQL daemon on most generic Linux servers:

* /sbin/service mysqld restart

If you do not have a my.cnf file, create a new one. The local path on most generic Linux servers is: /etc/my.cnf

To view MySQL variables and their current values, run the following command at the prompt:

* /usr/bin/mysql -uroot -pYOUR_ROOT_PASSWD

Then, run the following command (one single line) at MySQL prompt:

* show global variables like '%timeout%';

Which will show a result like this:

+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| connect_timeout | 5 |
| delayed_insert_timeout | 300 |
| innodb_lock_wait_timeout | 50 |
| interactive_timeout | 28800 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| slave_net_timeout | 3600 |
| table_lock_wait_timeout | 50 |
| wait_timeout | 28800 |
+-------------------------------+-------+
9 rows in set (0.00 sec)

As mentioned earlier, you can change the values of the connect_timeout and net_write_timeout. The higher the values are, the longer MySQL stays active and can process longer queries.

For more information about this error, go to: MySQL 5.0 Reference Manual :: B.1.2.9 MySQL

Reference: http://servertune.com/kbase/entry/366/