Mysql server has gone away windows server
This section also covers the related Lost connection to server during query error.
The most common reason for the MySQL server has gone away error is that the server timed out and closed the connection. In this case, you normally get one of the following error codes (which one you get is operating system-dependent).
| Error Code | Description |
|---|---|
| CR_SERVER_GONE_ERROR | The client couldn’t send a question to the server. |
| CR_SERVER_LOST | The client didn’t get an error when writing to the server, but it didn’t get a full answer (or any answer) to the question. |
By default, the server closes the connection after eight hours if nothing has happened. You can change the time limit by setting the wait_timeout variable when you start mysqld . See Section 5.1.8, “Server System Variables”.
If you have a script, you just have to issue the query again for the client to do an automatic reconnection. This assumes that you have automatic reconnection in the client enabled (which is the default for the mysql command-line client).
Some other common reasons for the MySQL server has gone away error are:
You (or the db administrator) has killed the running thread with a KILL statement or a mysqladmin kill command.
You tried to run a query after closing the connection to the server. This indicates a logic error in the application that should be corrected.
A client application running on a different host does not have the necessary privileges to connect to the MySQL server from that host.
You got a timeout from the TCP/IP connection on the client side. This may happen if you have been using the commands: mysql_options(. MYSQL_OPT_READ_TIMEOUT. ) or mysql_options(. MYSQL_OPT_WRITE_TIMEOUT. ) . In this case increasing the timeout may help solve the problem.
You have encountered a timeout on the server side and the automatic reconnection in the client is disabled (the reconnect flag in the MYSQL structure is equal to 0).
You are using a Windows client and the server had dropped the connection (probably because wait_timeout expired) before the command was issued.
The problem on Windows is that in some cases MySQL does not get an error from the OS when writing to the TCP/IP connection to the server, but instead gets the error when trying to read the answer from the connection.
The solution to this is to either do a mysql_ping() on the connection if there has been a long time since the last query (this is what Connector/ODBC does) or set wait_timeout on the mysqld server so high that it in practice never times out.
You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server’s max_allowed_packet variable, which has a default value of 64MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section B.3.2.8, “Packet Too Large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE .
It is also possible to see this error if host name lookups fail (for example, if the DNS server on which your server or network relies goes down). This is because MySQL is dependent on the host system for name resolution, but has no way of knowing whether it is working—from MySQL’s point of view the problem is indistinguishable from any other network timeout.
You may also see the MySQL server has gone away error if MySQL is started with the skip_networking system variable enabled.
Another networking issue that can cause this error occurs if the MySQL port (default 3306) is blocked by your firewall, thus preventing any connections at all to the MySQL server.
You can also encounter this error with applications that fork child processes, all of which try to use the same connection to the MySQL server. This can be avoided by using a separate connection for each child process.
You have encountered a bug where the server died while executing the query.
You can check whether the MySQL server died and restarted by executing mysqladmin version and examining the server’s uptime. If the client connection was broken because mysqld crashed and restarted, you should concentrate on finding the reason for the crash. Start by checking whether issuing the query again kills the server again. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.
You can obtain more information about lost connections by starting mysqld with the log_error_verbosity system variable set to 3. This logs some of the disconnection messages in the hostname.err file. See Section 5.4.2, “The Error Log”.
If you want to create a bug report regarding this problem, be sure that you include the following information:
Indicate whether the MySQL server died. You can find information about this in the server error log. See Section B.3.3.3, “What to Do If MySQL Keeps Crashing”.
If a specific query kills mysqld and the tables involved were checked with CHECK TABLE before you ran the query, can you provide a reproducible test case? See Section 5.9, “Debugging MySQL”.
What is the value of the wait_timeout system variable in the MySQL server? ( mysqladmin variables gives you the value of this variable.)
Have you tried to run mysqld with the general query log enabled to determine whether the problem query appears in the log? (See Section 5.4.3, “The General Query Log”.)
MySQL Server has gone away when importing large sql file
I tried to import a large sql file through phpMyAdmin. But it kept showing error
19 Answers 19
Two most common reasons (and fixes) for the MySQL server has gone away (error 2006) are:
Server timed out and closed the connection. How to fix:
check that wait_timeout variable in your mysqld’s my.cnf configuration file is large enough. On Debian: sudo nano /etc/mysql/my.cnf , set wait_timeout = 600 seconds (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart . I didn’t check, but the default value for wait_timeout might be around 28800 seconds (8 hours).
Server dropped an incorrect or too large packet. If mysqld gets a packet that is too large or incorrect, it assumes that something has gone wrong with the client and closes the connection. You can increase the maximal packet size limit by increasing the value of max_allowed_packet in my.cnf file. On Debian: sudo nano /etc/mysql/my.cnf , set max_allowed_packet = 64M (you can tweak/decrease this value when error 2006 is gone), then sudo /etc/init.d/mysql restart .
Edit:
Notice that MySQL option files do not have their commands already available as comments (like in php.ini for instance). So you must type any change/tweak in my.cnf or my.ini and place them in mysql/data directory or in any of the other paths, under the proper group of options such as [client] , [myslqd] , etc. For example:
Then restart the server. To get their values, type in the mysql client:
ERROR 2006 (HY000): MySQL server has gone away
I get this error when I try to source a large SQL file (a big INSERT query).
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:
Here is the file size:
When I try the other method.
21 Answers 21
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
You can increase Max Allowed Packet
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:
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.
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:
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.
Debugging
Here are few expert-level debug ideas:
Check the logs, e.g.
Test your connection via mysql , telnet or ping functions (e.g. mysql_ping in PHP).
Use tcpdump to sniff the MySQL communication (won’t work for socket connection), e.g.:
On Linux, use strace . On BSD/Mac use dtrace / dtruss , e.g.
Learn more how to debug MySQL server or client at: 26.5 Debugging and Porting MySQL.
For reference, check the source code in sql-common/client.c file responsible for throwing the CR_SERVER_GONE_ERROR error for the client command.
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:
Appending the flags —force —wait —reconnect to the command (i.e. mysql -u root -p -h localhost my_db ).
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)
Just in case, to check variables you can use
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
In my case the cnf file was not taken into account and I don’t know why, so the SET GLOBAL code really helped.
You can also log into the database as root (or SUPER privilege) and do
doesn’t require a MySQL restart as well. Note that you should fix your my.cnf file as outlined in other solutions:
And confirm the change after you’ve restarted MySQL:
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!
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):
I had the same problem but changeing max_allowed_packet in the my.ini/my.cnf file under [mysqld] made the trick.
now restart the MySQL service once you are done.
A couple things could be happening here;
- 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, like so;
$ mysql db_name 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.
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=1073741824 to /usr/local/etc/my.cnf
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:
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.
