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 = 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 maximal 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 = 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.
February 23rd, 2010 at 2:32
RJB THANK YOU!
FINALLY SOME USEFUL ADVICE! I have been trying to figure this out for hours and followed all this high tech advice that got me wrapped up in all sorts of things I didn’t need to be wasting my time with. I can’t believe it was as simple as clicking a box and no one else thought to mention that before handing out all these complicated suggestions.
February 24th, 2010 at 7:47
it works like a charm.. problem solved. thanks uploader
February 24th, 2010 at 16:27
Thanks, worked instantly.
March 25th, 2010 at 17:54
Careful with auto reconnection if any of these are important to you:
What are the side-effects of MySQL auto reconnect?
* Any active transactions are rolled back and autocommit mode is reset.
* All table locks are released.
* All TEMPORARY tables are closed (and dropped).
* Session variables are reinitialized to the values of the corresponding variables. This also affects variables that are set implicitly by statements such as SET NAMES.
* User variable settings are lost.
* Prepared statements are released.
* HANDLER variables are closed.
* The value of LAST_INSERT_ID() is reset to 0.
* Locks acquired with GET_LOCK() are released.
* mysql_ping() does not attempt a reconnection if the connection is down. It returns an error instead.
April 27th, 2010 at 8:58
Thanx man. Your info was useful!
May 4th, 2010 at 3:19
THANK YOU. I never would have even tried that little check box. Not even after reading this post. Now it makes sence. Thank you again.
July 31st, 2010 at 11:24
Your info was really useful! There are a lot of time need to wait in my site for answer for search query, and this error was a real problem! Thanx.
August 10th, 2010 at 4:55
thanks a lot, it solve my problem
when i insert 37306 record, i got error 2006
then i edit my.ini and add line
max_allowed_packet= 64M
wait_timeout= 6000
and finaly i got no error and succes insert the record
sorry i’m an indonesian, my english is too bad
October 26th, 2010 at 11:16
Thanx people and Suhu in particular:
max_allowed_packet= 64M
wait_timeout= 6000
my.ini
also worked for me!
December 10th, 2010 at 2:10
[...] reference: Workaround for WordPress database error How to fix “MySQL server has gone away†(error 2006) [...]
December 14th, 2010 at 11:10
Thanks Suhu,
your solution worked for me as well…
added
max_allowed_packet= 64M
wait_timeout= 6000
to my.ini file and it worked
December 14th, 2010 at 12:57
you’re welcome
http://thesuhu.wordpress.com/
December 29th, 2010 at 4:23
max_allowed_packet= 64M
wait_timeout= 6000
solution worked for me as well – thanks a lot
in my local wamp installation these were not in the config files – so just opening the file and going through did not help
Thanks for the info
January 2nd, 2011 at 23:03
It worked!
The file was my.ini in mysql\bin probably, because the version is another.
Thank you very much for this guide!!
March 8th, 2011 at 6:26
Thanks ,it really work for me, i just change the max_allowed_packet and my db is back , thanks for good information.
March 17th, 2011 at 8:04
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
March 23rd, 2011 at 16:04
GRACIAS !!!
May 24th, 2011 at 17:32
Rjb’s Solution implemented on large data sets is SLOW!!
June 16th, 2011 at 9:27
Thanks for this advices! For me personally setting of max_allowed_packet = 64M helped.
July 25th, 2011 at 10:12
Am using godaddy hosting, where can i locate my.ini , there’s only php.ini file
please help!
July 25th, 2011 at 12:04
my.ini is only available to server administrators; on a shared hosting, you normally would not have access to that file.
September 21st, 2011 at 19:25
If using PHP to feed a database and getting this MySQL error, instead of modifying my.ini, try to package your data in smaller strings and put them in an array.
Then, insert one pack at a time:
foreach ($pack_array as $pack) {
if ($pack != "") {
$query = "INSERT INTO mytable VALUES" . $pack;
$result = mysql_query($query);
if (!$result) die ("Database access failed: " . mysql_error());
}
}
November 23rd, 2011 at 15:21
I’m running a wamp server 1.7.2
…having error 2006 server has gone away when inserting large files into my database (nothing was inserted at all).
So, simply stop the wamp server services, edit the my.ini file thru the wamp manager icon.
The 2 lines :
max_allowed_packet= 64M
wait_timeout= 6000
were added at the end of the file (c:wampmysqlmy.ini)
Restart Wamp Server Services
And it works ! Thanks a lot !
January 8th, 2012 at 0:29
thx for the information, it’s work well to my db ^^
February 14th, 2012 at 19:50
RipleySW2,
You saved me! I was almost crying, trying to import babushkaslots.com mySQL DB! Luckily your advice worked like a charm and the dinner with my wife was saved!!!
BTW, WAMP have a really nice interface to get to the config. files
Thanks again,
Hungry Tim
March 16th, 2012 at 14:16
What can I do when those two entries
max_allowed_packet= 64M
wait_timeout= 6000
are not enough to restore a 82MB database via
mysql> \. thedumpfile.dmp
I am fiddling around for 2 days now without any success – any other idea than the two lines? (MySQL Server 5.5.16)
March 22nd, 2012 at 23:14
@Badera,
if that entire dump is wrapped in a transaction, then you may need to set max_allowed_packet to e.g. 96MB (basically, anything larger than your dump size). But that’s just a guess.
March 23rd, 2012 at 10:23
@Bogdan:
I also tried 128MB for the 82MB dump file, no success.
March 23rd, 2012 at 22:20
@Badera, do you really get error 2006? Maybe your error is just different.
March 28th, 2012 at 10:54
Hi Guys,
When i try reconnecting after 5mins sleep/idle to mysql db from remote host it’s lossing it connection with the following error.
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 19068867
Current database: *** NONE ***
March 30th, 2012 at 12:36
-.-
It IS definitely Error 2006 (HY000).
March 31st, 2012 at 0:11
@Badera, did you restart the MySQL server after changing configuration?
Also, just before attempting the import, try
from within the mysql-client session (or just add this statement at the very top of the SQL file you are importing). I have no idea if this statement still works in current versions of MySQL – please post if it does [not].
April 12th, 2012 at 7:02
Very much cool this tip!!
It works so good to me!!
Thanks so much!!!
August 17th, 2012 at 16:27
[...] http://bogdan.org.ua/2008/12/25/how-to-fix-mysql-server-has-gone-away-error-2006.html [...]
August 21st, 2012 at 11:54
Thanks – Actually saved my life.
August 23rd, 2012 at 13:22
I had the similar issue, after a lot of research we figured that the issue was with thte F5 load balancer timeout setting.
October 17th, 2012 at 15:11
[...] ЕÑли Ð’Ñ‹ получаете ошибку MySQL server has gone away (error 2006) при иÑпользовании драйвера MySQL ODBC – можете попробовать Ñто решение. [...]
October 29th, 2012 at 8:49
[...] http://bogdan.org.ua/2008/12/25/how-to- … -2006.html [...]
December 19th, 2012 at 19:43
Thank you.
Solved commenting the line: wait_timeout=10
I leave it by default. I need to play more… testing with different values to better adjust.
February 24th, 2013 at 11:06
Thanks for the fix, the error has gone.
March 11th, 2014 at 19:41
[…] How to fix “MySQL server has gone away†(error 2006) […]
September 8th, 2014 at 1:08
not “max_allowed_packet=” in centos.?
March 6th, 2015 at 8:55
[…] Оригинал иÑходной Ñтатьи (на англ): How to fix “MySQL server has gone away†(error 2006) […]
April 14th, 2015 at 23:15
Hello
Where to find this my.ini file using FTP client??
I can not find it.
FTP is the only access i have in my hosting provider.
thanks
April 19th, 2015 at 23:10
Panter011,
if you only have FTP access, then highly likely you do not have access to the server’s my.ini.
If you get this “error 2006″, there are two options for you:
- modify your website’s code, so that it retrieves/writes data in smaller chunks; this is not guaranteed to, but may help;
- contact your hosting’s support, especially if you already read/write data in small chunks (e.g. several rows at a time, not thousands).
April 25th, 2015 at 5:21
I am setting up wamp on my machine to run wordpress and I keep getting this error when I access wordpress with localhost:2000, don’t think in my case it is a matter of a large file, something else is wrong, any suggestions?
March 28th, 2018 at 6:35
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.
April 25th, 2018 at 8:31
Thanks !! It really worked out error has gone.