MySQL server has gone away

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

 Answers

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

---

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

---

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.

---

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

---

I solved the error ERROR 2006 (HY000) at line 97: MySQL server has gone away and successfully migrated a >5GB sql file by performing these two steps in order:

1. Created /etc/my.cnf as others have recommended, with the following contents:

   [mysql]
   connect_timeout = 43200
   max_allowed_packet = 2048M
   net_buffer_length = 512M
   debug-info = TRUE
   

2. Appending the flags --force --wait --reconnect to the command (i.e. mysql -u root -p -h localhost my_db < file.sql --verbose --force --wait --reconnect).

Important Note: It was necessary to perform both steps, because if I didn't bother making the changes to /etc/my.cnf file as well as appending those flags, some of the tables were missing after the import.

System used: OSX El Capitan 10.11.5; mysql Ver 14.14 Distrib 5.5.51 for osx10.8 (i386)

---

For more information on this refer to http://dev.mysql.com/doc/refman/5.5/en/gone-away.html or http://dev.mysql.com/doc/refman/5.1/en/gone-away.html as appropriate.

---

I encountered this error when I use Mysql Cluster, I do not know this question is from a cluster usage or not. As the error is exactly the same, so give my solution here. Getting this error because the data nodes suddenly crash. But when the nodes crash, you can still get the correct result using cmd:

ndb_mgm -e 'ALL REPORT MEMORYUSAGE'

And the mysqld also works correctly.So at first, I can not understand what is wrong. And about 5 mins later, ndb_mgm result shows no data node working. Then I realize the problem. So, try to restart all the data nodes, then the mysql server is back and everything is OK.

But one thing is weird to me, after I lost mysql server for some queries, when I use cmd like show tables, I can still get the return info like 33 rows in set (5.57 sec), but no table info is displayed.

---

This is more of a rare issue but I have seen this if someone has copied the entire /var/lib/mysql directory as a way of migrating their DB to another server. The reason it doesn't work is because the database was running and using log files. It doesn't work sometimes if there are logs in /var/log/mysql. The solution is to copy the /var/log/mysql files as well.

---

if none of this answers solves you the problem, I solved it by removing the tables and creating them again automatically in this way:

when creating the backup, first backup structure and be sure of add:
DROP TABLE / VIEW / PROCEDURE / FUNCTION / EVENT
CREATE PROCEDURE / FUNCTION / EVENT
IF NOT EXISTS
AUTO_INCREMENT

then just use this backup with your db and it will remove and recreate the tables you need.

Then you backup just data, and do the same, and it will work.