Monday, August 23, 2010

Troubleshooting : mysql server has gone away

When running query against a database the following error can be generated:

ERROR 2006 (HY000) at line NNN: MySQL server has gone away

Where "NNN" is the line number of the script currently being run where the error occurred.

Possible Causes and Resolution

This is a general error which can have a number of possible causes. The one certainty is that the MySQL database is no longer listening on the connection - but this does not necessarily mean that MySQL is no longer available.

Possible causes:

MySQL has crashed.
Check that MySQL is still running on the server. If it is not running and this was not a planned shutdown then it is reasonable to conclude that MySQL has crashed. I personally have never experienced this, but if it happens then investigate whether you can upgrade to a newer version.

MySQL has been shutdown.
Check that MySQL is still running, if it is not and it was shut down (accidentally or intentionally) then that is the cause of the problem. Restart MySQL.

Network connectivity has failed.
Even if the database is still up, is it possible to contact the server? If you can ping the server (or MySQL is running no the localhost) then basic network connectivity is not the problem.

MySQL networking has failed
The problem may be the MySQL connection. If the query was trying to transfer a large block of data then the MySQL packet-size may be set too small.

Try increasing the value of max_allowed_packet and restart the MySQL database service. In MySQL Administrator select "Startup Variables" andon the "Advanced Networking" tab thsi is shown under "Data / Memory size" and is labeled as "Max. packet size." The default is 1MB and one of my systems now runs at 16MB.

The database connection has timed out and thus the SQL has failed.
Try increasing the wait_timeout, or reconnecting just before firing the query.

Modified after copying from http://www.cryer.co.uk/brian/mysql/trouble_mysql_gone_away.htm

2 comments:

Maciej Wiercinski said...

Another possibility you haven't mentioned is that the query has been killed.

Mark Willium said...

You can also follow these steps to resolve the issue:

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

Regards,
MySQL Repair