ERROR 2006 (HY000): MySQL server has gone away - Stack Overflow
Join Stack Overflow to learn, share knowledge, and build your career.
or sign in with
I get this error when I try to source a large SQL file (a big INSERT query).
source file.sql
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
Current database: *** NONE ***
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:
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
+--------------------+---------+
| 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
9,9002178144
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 .
On Windows this file is located at: "C:\ProgramData\MySQL\MySQL Server
On Linux (Ubuntu): /etc/mysql
19.4k62139
4,66811314
You can increase Max Allowed Packet
SET GLOBAL max_allowed_packet=;
7,46633940
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= &databasename& & db.sql
19.5k84265
In general the error:
Error: 2006 (CR_SERVER_GONE_ERROR) - MySQL server has gone away
means that the client couldn't send a question to the server.
mysql import
In your specific case while importing the database file via mysql, this most likely mean that some of the queries in the SQL file are too large to import and they couldn't be executed on the server, therefore client fails on the first occurred error.
So you've the following possibilities:
Add force option (-f) for mysql to proceed and execute rest of the queries.
This is useful if the database has some large queries related to cache which aren't relevant anyway.
Increase max_allowed_packet and wait_timeout in your server config (e.g. ~/.my.cnf).
Dump the database using --skip-extended-insert option to break down the large queries. Then import it again.
Try applying --max-allowed-packet option for mysql.
Common reasons
In general this error could mean several things, such as:
a query to the server is incorrect or too large,
Solution: Increase max_allowed_packet variable.
Make sure the variable is under [mysqld] section, not [mysql].
Don't afraid to use large numbers for testing (like 1G).
Don't forget to restart the MySQL/MariaDB server.
Double check the value was set properly by:
mysql -sve "SELECT @@max_allowed_packet" # or:
mysql -sve "SHOW VARIABLES LIKE 'max_allowed_packet'"
You got a timeout from the TCP/IP connection on the client side.
Solution: Increase wait_timeout variable.
You tried to run a query after the connection to the server has been closed.
Solution: A logic error in the application should be corrected.
Host name lookups failed (e.g. DNS server issue), or server has been started with --skip-networking option.
Another possibility is that your firewall blocks the MySQL port (e.g. 3306 by default).
The running thread has been killed, so retry again.
You have encountered a bug where the server died while executing the query.
A client running on a different host does not have the necessary privileges to connect.
And many more, so learn more at: .
Here are few expert-level debug ideas:
Check the logs, e.g.
sudo tail -f $(mysql -Nse "SELECT @@GLOBAL.log_error")
Test your connection via mysql, telnet or ping functions (e.g.
Use tcpdump to sniff the MySQL communication (won't work for socket connection), e.g.:
sudo tcpdump -i lo0 -s 1500 -nl -w- port mysql | strings
On Linux, use strace. On BSD/Mac use dtrace/dtruss, e.g.
sudo dtruss -a -fn mysqld 2&&1
Learn more how to debug MySQL server or client at: .
For reference, check the source code in
file responsible for throwing the CR_SERVER_GONE_ERROR error for the client command.
MYSQL_TRACE(SEND_COMMAND, mysql, (command, header_length, arg_length, header, arg));
if (net_write_command(net,(uchar) command, header, header_length,
arg, arg_length))
set_mysql_error(mysql, CR_SERVER_GONE_ERROR, unknown_sqlstate);
48.1k18309305
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=
In my case the cnf file was not taken into account and I don't know why, so the SET GLOBAL code really helped.
1,38052338
I had the same problem but changeing max_allowed_packet in the my.ini/my.cnf file under [mysqld] made the trick.
add a line
max_allowed_packet=500M
now restart the MySQL service once you are done.
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):
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
You can also log into the database as root (or SUPER privilege) and do
set global max_allowed_packet=64*;
doesn't require a MySQL restart as well. Note that you should fix your my.cnf file as outlined in other solutions:
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!
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:
Created /etc/my.cnf as others have recommended, with the following contents:
connect_timeout = 43200
max_allowed_packet = 2048M
net_buffer_length = 512M
debug-info = TRUE
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)
19.5k84265
1,39511517
A couple things cou
Your INSERT is running long, and client is disconnecting. When it reconnects it's not selecting a database, hence the error. One option here is to run your batch file from the command line, and select the database in the arguments,
$ mysql db_name & source.sql
Another is to run your command via php or some other language. After each long - running statement, you can close and re-open the connection, ensuring that you're connected at the start of each query.
9,83332229
For more information on this refer to
as appropriate.
1,84711640
If you are on Mac and installed mysql through brew like me, the following worked.
cp $(brew --prefix mysql)/support-files/my-default.cnf /usr/local/etc/my.cnf
add max_allowed_packet= to /usr/local/etc/my.cnf
mysql.server restart
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.
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
they will be monitoring this if they are competent anyway.
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.
For amazon RDS (it's my case), you can change the max_allowed_packet parameter value to any numeric value in bytes that makes sense for the biggest data in any insert you may have (e.g.: if you have some 50mb blob values in your insert, set the max_allowed_packet to 64M = ), in a new or existing parameter-group. Then apply that parameter-group to your MySQL instance (may require rebooting the instance).
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.
2,77712652
How about using the mysql client like this:
mysql -h &hostname& -u username -p &databasename& & file.sql
2,76772848
protected by ♦
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10
on this site (the ).
Would you like to answer one of these
Not the answer you're looking for?
Browse other questions tagged
Upcoming Events
Stack Overflow works best with JavaScript enabled欢迎来到没得比！
7日0点前100名！林氏木业 1004 布艺沙发床脚踏组合 三人位+脚踏
1019元包邮（双重优惠）
7日0点前100名！林氏木业 1004 布艺沙发床脚踏组合 三人位+脚踏
1019元包邮（双重优惠）
爆料时间： 18:15:25
配　　送：
促销类型：
该条信息被推送到首页精华，奖励150铜币！ 　|　
比友“欧阳”爆料原文：
林氏木业这款布艺沙发组合，定位经济型，带脚踏。比较适合小户型客厅。米白色洁净清新，驾驭日式简约风格恰到好处，蓝色款纯净稳重，更能成为客厅主视觉。沙发靠背90°-180°调节，方便躺卧；三人位沙发尺寸mm，加上贵妃位长度可增加到1630mm，变身沙发床后尺寸是mm，给小户型客厅增加一个床位却不占空间。扶手兼具茶几功能，槽状设计防止酒水打翻弄脏沙发，扶手反面打开是储物格，常用的书籍电子设备可以存放在里面，另一侧扶手有双面储物袋，遥控器等小物件有了专属位置，布艺沙发棉麻面料，可拆洗。某猫当前预告的3.7女王节特价是2098元，前100名拍下付款者可获赠1049元的红包，叠加，最低可以1019元的价格入手，选购小户型布艺储物沙发的朋友不可错过。
小编补充：
电商的特价促销实时更新，页面展示的特价随时可能涨价，所以大家不同时段点进商品界面的价格很可能不一样，错过好价请务必淡定，实时关注好价请
并开启推送功能
爆料很好，犒劳一下
Meidebi.com 狸狗信息网推荐著名文本编辑软件UltraEdit-32 15.10.0.1019 官方简体中文版 | 侠客岛}