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

max_allowed_packet=64M 

Adding this line into my.cnf file solves my problem.

This is useful when the columns have large values, which cause the issues, you can find the explanation here.

On Windows this file is located at: "C:\ProgramData\MySQL\MySQL Server 5.6"

On Linux (Ubuntu): /etc/mysql

Answer 2

You can increase Max Allowed Packet

SET GLOBAL max_allowed_packet=1073741824; 

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_allowed_packet