Autarchy of the Private Cave

Tiny bits of bioinformatics, [web-]programming etc

    • Archives

    • Recent comments

    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:

    1. 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).
    2. 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.

    Share

    54 Responses to “How to fix “MySQL server has gone away” (error 2006)”

    1. حرب الملوك Says:

      Thanx

      I’ll try this fix and see what happen

    2. Mabooka-Mabooka Says:

      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!

    3. Bogdan Says:

      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.

    4. Piscal Pisali Says:

      Thanks for the reports. I am getting same error while restoring database.
      Regards

    5. RJB Says:

      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.

    6. Bogdan Says:

      RJB – thanks, I’ve updated post text to point at your comment.

    7. GBo Says:

      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.

    8. mars Says:

      it works like a charm.. problem solved. thanks uploader

    9. Sargo Darya Says:

      Thanks, worked instantly.

    10. ccspro Says:

      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.

    11. Pavlo Says:

      Thanx man. Your info was useful!

    12. wingnut Says:

      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.

    13. Andrew Says:

      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.

    14. suhu Says:

      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

    15. Peter Schuitemaker Says:

      Thanx people and Suhu in particular:
      max_allowed_packet= 64M
      wait_timeout= 6000
      my.ini
      also worked for me!

    16. 2006 mysql server has gone away | Shopping Cart Software Solutions Says:

      [...] reference: Workaround for WordPress database error How to fix “MySQL server has gone away” (error 2006) [...]

    17. sandy Says:

      Thanks Suhu,
      your solution worked for me as well…
      added

      max_allowed_packet= 64M
      wait_timeout= 6000

      to my.ini file and it worked

    18. suhu Says:

      you’re welcome

      http://thesuhu.wordpress.com/

    19. Raja Says:

      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

    20. Rosen Karamanov Says:

      It worked!
      The file was my.ini in mysql\bin probably, because the version is another.
      Thank you very much for this guide!!

    21. shahid Says:

      Thanks ,it really work for me, i just change the max_allowed_packet and my db is back , thanks for good information.

    22. registry cleaner Says:

      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

    23. mckowell Says:

      GRACIAS !!!

    24. Johnnytest Says:

      Rjb’s Solution implemented on large data sets is SLOW!!

    25. Igor Zemliansky Says:

      Thanks for this advices! For me personally setting of max_allowed_packet = 64M helped.

    26. chetan Says:

      Am using godaddy hosting, where can i locate my.ini , there’s only php.ini file
      please help!

    27. Bogdan Says:

      my.ini is only available to server administrators; on a shared hosting, you normally would not have access to that file.

    28. massa Says:

      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());
      }
      }

    29. RipleySW2 Says:

      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 !

    30. grosir jam tangan Says:

      thx for the information, it’s work well to my db ^^

    31. Tim Traxly Says:

      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
      :)

    32. badera Says:

      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)

    33. Bogdan Says:

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

    34. badera Says:

      @Bogdan:
      I also tried 128MB for the 82MB dump file, no success.

    35. Bogdan Says:

      @Badera, do you really get error 2006? Maybe your error is just different.

    36. SKS Says:

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

    37. badera Says:

      -.-
      It IS definitely Error 2006 (HY000).

    38. Bogdan Says:

      @Badera, did you restart the MySQL server after changing configuration?

      Also, just before attempting the import, try

      set session max_allowed_packet=128000000;

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

    39. André Says:

      Very much cool this tip!!
      It works so good to me!!
      Thanks so much!!!

    40. MySQL–Restoring large database on windows. « The .Net Developer Blog Says:

      [...] http://bogdan.org.ua/2008/12/25/how-to-fix-mysql-server-has-gone-away-error-2006.html [...]

    41. WQ Says:

      Thanks – Actually saved my life.

    42. sks228 Says:

      I had the similar issue, after a lot of research we figured that the issue was with thte F5 load balancer timeout setting.

    43. Как исправить “MySQL server has gone away” (error 2006) Says:

      [...] Если Вы получаете ошибку MySQL server has gone away (error 2006) при использовании драйвера MySQL ODBC – можете попробовать это решение. [...]

    44. Error: MySQL server has gone away | Open Cart Know How Says:

      [...] http://bogdan.org.ua/2008/12/25/how-to- … -2006.html [...]

    45. Rororo Says:

      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.

    46. IMD Says:

      Thanks for the fix, the error has gone.

    47. Error 2006 en MySQL | #blogeando Says:

      […] How to fix “MySQL server has gone away” (error 2006) […]

    48. Hendri Says:

      not “max_allowed_packet=” in centos.?

    49. Как исправить “MySQL server has gone away” (error 2006)Блог молодого вебмастера | Блог молодого РSays:

      […] Оригинал исходной статьи (на англ): How to fix “MySQL server has gone away” (error 2006) […]

    50. panter011 Says:

      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

    51. Bogdan Says:

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

    52. Webby Says:

      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?

    53. Blackjuker Says:

      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.

    54. Angbuhang Says:

      Thanks !! It really worked out error has gone.

    Leave a Reply

    XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>