ERROR 2006 (HY000): MySQL server has gone away

Question

I get this error when I try to source a large SQL file (a big INSERT query).

mysql>  source file.sql
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

Nothing in the table is updated. I've tried deleting and undeleting the table/database, as well as restarting MySQL. None of these things resolve the problem.

Here is my max-packet size:

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+

Here is the file size:

$ ls -s file.sql 
79512 file.sql

When I try the other method...

$ ./mysql -u root -p my_db < file.sql
Enter password: 
ERROR 2006 (HY000) at line 1: MySQL server has gone away

Answer 1

Just in case, to check variables you can use

$> mysqladmin variables -u user -p 

This will display the current variables, in this case max_allowed_packet, and as someone said in another answer you can set it temporarily with

mysql> SET GLOBAL max_allowed_packet=1072731894

In my case the cnf file was not taken into account and I don't know why, so the SET GLOBAL code really helped.

Answer 2

If it's reconnecting and getting connection ID 2, the server has almost definitely just crashed.

Contact the server admin and get them to diagnose the problem. No non-malicious SQL should crash the server, and the output of mysqldump certainly should not.

It is probably the case that the server admin has made some big operational error such as assigning buffer sizes of greater than the architecture's address-space limits, or more than virtual memory capacity. The MySQL error-log will probably have some relevant information; they will be monitoring this if they are competent anyway.

Answer 3

The global update and the my.cnf settings didn't work for me for some reason. Passing the max_allowed_packet value directly to the client worked here:

mysql -h <hostname> -u username -p --max_allowed_packet=1073741824 <databasename> < db.sql

Answer 4

How about using the mysql client like this:

mysql -h <hostname> -u username -p <databasename> < file.sql

Answer 5

You can also log into the database as root (or SUPER privilege) and do

set global max_allowed_packet=64*1024*1024;

doesn't require a MySQL restart as well. Note that you should fix your my.cnf file as outlined in other solutions:

[mysqld]
max_allowed_packet=64M

And confirm the change after you've restarted MySQL:

show variables like 'max_allowed_packet';

You can use the command-line as well, but that may require updating the start/stop scripts which may not survive system updates and patches.

As requested, I'm adding my own answer here. Glad to see it works!

Answer 6

The solution is increasing the values given the wait_timeout and the connect_timeout parameters in your options file, under the [mysqld] tag.

I had to recover a 400MB mysql backup and this worked for me (the values I've used below are a bit exaggerated, but you get the point):

[mysqld]
port=3306
explicit_defaults_for_timestamp = TRUE
connect_timeout = 1000000
net_write_timeout = 1000000
wait_timeout = 1000000
max_allowed_packet = 1024M
interactive_timeout = 1000000
net_buffer_length = 200M
net_read_timeout = 1000000