How to fix “MySQL server has gone away” (error 2006)
25th December 2008
Source: George from vbulletin team.
Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:
- Server timed out and closed the connection. How to fix: check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough. On Debian: sudo nano /etc/mysql/my.cnf, set wait_timeout to at least 600 seconds (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart. I didn’t check, but the default value for wait_timeout might be around 28800 seconds (8 hours).
- Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. On Debian: sudo nano /etc/mysql/my.cnf, set max_allowed_packet to to something like 64M (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart.
If you get MySQL server has gone away (error 2006) when using MySQL ODBC driver – give this hint a try.
July 24th, 2009 at 19:37
Thanx
I’ll try this fix and see what happen
August 28th, 2009 at 6:44
Thank for this useful reply!
(Unfortunately, in search results, it comes way after some very old and ~mostly~ meaningless discussion going back into 2003 — http://community.norton.com/norton/board?board.id=norton_mac ).
Well, it’s 2009 now, and it seems that this is still a problem (lacking unambiguous explanation and a single recipe to solve it) for some people. (2003+2009)/2.
I’m seeing it for the 1st time in my life — on Mac OS X (Leopard). I am confused. Please read on.
===
I am sure that in my case the size (of the query + data) is not the reason: the query it drops off on is a very simple select from a single small table. On the other hand, this query might be sent quite often (say 100 times / second), and that’s perhaps the reason why (?in my case).
I’d like to:
1) ask you to elaborate: what is “an incorrect or too large packet”? Specifically — “incorrect”.
(My server is multi-threaded and, at least in theory, queries might come from diff. threads ~~almost simultaneously: does MySQL handle this appropriately?).
2) In my case, fixing this by tweaking the *.cnf is not exactly what I am looking for.
If a mysql server cannot handle smth., I’ll scale horizontally; but at the same time i need to know ***how and what to measure***. So the question is: what (now officially from MySQL GURUs) can cause this 2006 error exactly?
BTW:
3) I sometimes see the “2006″ accompanied by this:
2014, “Commands out of sync; you can’t run this command now”
And, I am reading here and there about “”"~~~packets coming out of order~~~”"”.
What the hell does THAT supposed to mean? How the commands ***may*** come out of sync, who is responsible? And, ab. “packets out of order”: does it imply that the TCP layer is “out of order”? (I doubt it very much).
3) For the worst case scenario:
I need to fix this in real time; in a worst case, I’ll loose an incoming message but I need to continue serving others non-stop.
The question is: how to detect this particular error and continue (probably re-connect?) wisely?
~~~
Please advice some more:-).
// I think it is unproductive to discuss whether this is a bug or a feature or something else. It is definitely an issue for many.
Thanks in advance!
August 29th, 2009 at 12:43
hey, I’m not one of MySQL developers
1), 2) To the best of my knowledge, MySQL is ‘thread-safe’ for writing operations, because locking is used (table-level for MyISAM or row-level for InnoDB, if I’m not mistaken). So I guess ‘incorrect’ has nothing to do with multi-threading. Other than that, I would advise searching for this error on MySQL website – they do have nice explanations, although one may have to search deeper to find those nice explanations. Also, I would advise trying to increase packet size in my.cnf – just to see if that helps.
3) I have no idea what 2014 means, I’ve never had this error, and thus have never had a look at its description. Also, I’m using Unix sockets to connect to MySQL, not TCP port 3306 (on a single machine).
4) You can detect any error using standard means of your programming/scripting language of choice (e.g. PHP, Python, Ruby). You can do this either at application layer, at some common database layer, or even both. Exact answer depends on your specific software architecture. As for continuing: again, you can sleep(couple of seconds) and then re-establish MySQL connection using standard means of your programming language of choice.
Also, you can monitor mysql.error alterations (system-wide), and perform some action (e.g. graceful restart
) as soon as log has ‘2006′ error entry. The simplest way to do this is a bash script, doing ‘tail -f mysql.error | grep 2006′ and restarting mysql whenever new error line appears 
Finally, do have a look at error description on MySQL website. That is an enlightening experience.
October 11th, 2009 at 20:11
Thanks for the reports. I am getting same error while restoring database.
Regards
January 21st, 2010 at 16:04
I came across this error message while experimenting with the MySQL ODBC 5.1 Driver.
I am considering transferring my Access databases to a MySQL server, but some of my users are accustomed to using Access on the front end. To make the change transparent for them, I may have them use Access with tables linked to the MySQL database via the ODBC driver.
When configuring the Machine Data Source for the ODBC connection, the MySQL Connector/ODBC GUI opens. By expanding the “Details”, selecting tab “Flag 1” and checking the “Enable automatic reconnect” option, the “MySQL server has gone away (error 2006)” problem was eliminated.
This was preferred over messing with the cnf files. I hope this helps.
January 21st, 2010 at 17:26
RJB – thanks, I’ve updated post text to point at your comment.