MySQL server has gone away

Today I am trying to restore one of MySQL dump into my development database.
While I executing the mysql -uroot -p XXXX < XXXX.sql, the restoration show me the error of “ERROR 2006 (HY000) at line 3051: MySQL server has gone away“.

Then I double check on the database that I restore, I found that the action terminated in the middle & some of the tables & data is not restored.

I googled a while & found the solution.

The problem occur due to MySQL default installation setting in my.ini. the default value fr “max_allowed_packet” is 1M. This is the problem causing the restoration of the database terminated.

So I go to my.ini that located in “C:\Program Files\MySQL\MySQL Server 5.0” & add in the following value in the [mysqld] (make sure that you add in at the mysqld session, if not it will not take effect) session.

max_allowed_packet = 32M

After completed, restart your MySQL services & it should be working. Lastly, you may also checking your MySQL setting by following steps:

  1. Login into MySQL using “mysql -uroot -p“
  2. Type the following command to check your max packet allow size
    “show variables like ‘max_allowed_packet’;” 

    Result

7 Comments to “MySQL server has gone away”

  1. ddp 22 February 2012 at 12:32 am #

    I did what you said and when I check the max allowed packet through command line I get the default size of 1M although I have edited and saved the my.ini file and set the size to 32M. Thanks for your help anyway, very well written guide.

    Reply
  2. ddp 22 February 2012 at 5:36 pm #

    OK, my mistake – to my defense it was a long day – I had to restart the machine. Thanks again.

    Reply
  3. soistheman 26 April 2012 at 10:50 pm #

    It worked!!

    Reply
  4. salut 31 October 2012 at 11:03 pm #

    This is a great solution. It was quite hard to figure out why freshly dumped sql files with mysqladministrator would not be restored. I did notice that it something to do with large blobs, but… this… this saved my ass 🙂 You really dug deep for this one !

    THANK YOU !!!!

    Reply
  5. sumi 9 April 2013 at 3:24 pm #

    Thanks lot.!!

    Reply
  6. „Mysql server has gone away” i przywracanie bazy « Technologie Internetowe

    […] http://www.how2guru.com/archives/mysql-server-has-gone-away/ […]

    Reply
  7. CarlosFrias 5 July 2013 at 8:14 am #

    Thanks a lot, i was trying all kind of ways with no success… IT WORKED thanks!!

    Reply

Leave a Reply