Menu
I have a MYSQL dump from a database that I am trying to move to a new db server. When I try to import my sql dump, I receive the following error:
MySQL Error 2006 (HY000) at line 406: MySQL server has gone away
I googled the problem and most people fixed the problem by changing the value of wait_timeout. However, my current value is set to 28800 (8 hours) and the error appears in less than 8 seconds when I run the import.
I also tried setting the value of max_allowed_packet to 1073741824 but that also did not fix the problem.
Looking through the mysql dump, there are quite a few blob columns in the dump, but the overall file size is only 6 MB.
Does anyone have any ideas about what else might be the problem?
900
The MySQL server has gone away (error 2006) has two main causes and solutions: Server timed out and closed the connection. To fix, check that wait_timeout mysql variable in your my. cnf configuration file is large enough, eg wait_timeout = 28800.
Raising it in /etc/my. cnf (under [mysqld] ) to 8 or 16M usually fixes it. (The default in MySql 5.7 is 4194304 , which is 4MB.) Note: This can be set on your server as it's running.
The MySQL server has gone away error, means that MySQL server (mysqld) timed out and closed the connection. By default, MySQL will close connections after eight hours (28800 seconds) if nothing happens.
Adding this answer for the benefit of future searchers, as it explains why increasing the packet size fixed the problem:
The situation is that if a client sends a SQL-statement longer than the server max_allowed_packet setting, the server will simply disconnect the client. Next query from the same client instance will find that the ‘MySQL server has gone away’.
... But it would of course be much preferable to have the ‘got packet bigger’ error [Error: 2020 (CR_NET_PACKET_TOO_LARGE)] returned if that is the problem.
Excerpted from and thanks for peter_laursen's blog post
On OSX 10.7 (Lion), I created a file, /etc/my.cnf with the following contents:
[mysqld]
max_allowed_packet = 12000000
And then stopped the mysql server:
/usr/local/bin/mysql.server stop
When it automatically restarted I was able to execute my inserts.
131
Increasing max_allowed_packet to 12 MB (12000000) solved the problem for me when trying to import a 130 MB file.
Change the ini file or under Options File / Networking in MySQL Workbench (MySQL restart required).
If you still get the error, try increasing even more (100 MB). Just remember to decrease it when you're done.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
