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:
- Login into MySQL using “mysql -uroot -p“
- Type the following command to check your max packet allow size
“show variables like ‘max_allowed_packet’;”Result
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.
OK, my mistake – to my defense it was a long day – I had to restart the machine. Thanks again.
It worked!!
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 !!!!
Thanks lot.!!
[…] http://www.how2guru.com/archives/mysql-server-has-gone-away/ […]
Thanks a lot, i was trying all kind of ways with no success… IT WORKED thanks!!