MySQL Server has gone away when importing large sq

Question

I tried to import a large sql file through phpMyAdmin...But it kept showing error

'MySql server has gone away'

What to do?

Answer 1

The other reason this can happen is running out of memory. Check /var/log/messages and make sure that your my.cnf is not set up to cause mysqld to allocate more memory than your machine has.

Your mysqld process can actually be killed by the kernel and then re-started by the "safe_mysqld" process without you realizing it.

Use top and watch the memory allocation while it's running to see what your headroom is.

make a backup of my.cnf before changing it.

Answer 2

If you are running with default values then you have a lot of room to optimize your mysql configuration.

The first step I recommend is to increase the max_allowed_packet to 128M.

Then download the MySQL Tuning Primer script and run it. It will provide recommendations to several facets of your config for better performance.

Also look into adjusting your timeout values both in MySQL and PHP.

How big (file size) is the file you are importing and are you able to import the file using the mysql command line client instead of PHPMyAdmin?

Answer 3

For GoDaddy shared hosting

On GoDaddy shared hosting accounts, it is tricky to tweak the PHP.ini etc files. However, there is another way and it just worked perfectly for me. (I just successfully uploaded a 3.8Mb .sql text file, containing 3100 rows and 145 cols. Using the IMPORT command in phpMyAdmin, I was getting the dreaded MySQL server has gone away error, and no further information.)

I found that Matt Butcher had the right answer. Like Matt, I had tried all kinds of tricks, from exporting MySQL databases in bite-sized chunks, to writing scripts that break large imports into smaller ones. But here is what worked:

(1) CPANEL ---> FILES (group) ---> BACKUP

(2a) Under "Partial Backups" heading...
(2b) Under "Download a MySQL Database Backup"
(2c) Choose your database and download a backup (this step optional, but wise)

(3a) Directly to the right of 2b, under heading "Restore a MySQL Database Backup"
(3b) Choose the .SQL import file from your local drive
(3c) True happiness will be yours (shortly....) Mine took about 5 seconds

I was able to use this method to import a single table. Nothing else in my database was affected -- but that is what step (2) above is intended to protect against.

Notes:
a. If you are unsure how to create a .SQL import file, use phpMyAdmin to export a table and modify that file structure.

SOURCE: Matt Butcher 2010 Article

Answer 4

If increasing max_allowed_packet doesn't help.

I was getting the same error as you when importing a .sql file into my database via Sequel Pro.

The error still persisted after upping the max_allowed_packet to 512M so I ran the import in the command line instead with:

mysql --verbose -u root -p DatabaseName < MySQL.sql

It gave the following error:

ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled

I found a couple helpful StackOverflow questions:

• Enable binary mode while restoring a Database from an SQL dump
• Mysql ERROR: ASCII '\0' while importing sql file on linux server

In my case, my .sql file was a little corrupt or something. The MySQL dump we get comes in two zip files that need to be concatenated together and then unzipped. I think the unzipping was interrupted initially, leaving the file with some odd characters and encodings. Getting a fresh MySQL dump and unzipping it properly worked for me.

Just wanted to add this here in case others find that increasing the max_allowed_packet variable was not helping.

Answer 5

I am doing some large calculations which involves the mysql connection to stay long time and with heavy data. i was facing this "Mysql go away issue". So i tried t optimize the queries but that doen't helped me then i increased the mysql variables limit which is set to a lower value by default.

wait_timeout max_allowed_packet

To the limit what ever suits to you it should be the Any Number * 1024(Bytes). you can login to terminal using 'mysql -u username - p' command and can check and change for these variable limits.

Answer 6

As stated here:

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:

1. 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).

2. 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 :
[mysqld]
wait_timeout = 600
max_allowed_packet = 64M
Then restart the server. To get the their values, type in the console:
select @@wait_timeout;
select @@max_allowed_packet;