Keep getting “MySQL server has gone away” errors during command line import

Question

I'm using the following command to import data from a 160 MB file created on another server with mysqldump. The environment I'm importing to is running the latest WAMP Server with max_allowed_packet set to "16M".

C:\wamp\bin\mysql\mysql5.1.36\bin>mysql --max_allowed_packet=15M -u root -pXXX drupal < C:\prod.sql

ERROR 2006 (HY000) at line 644: MySQL server has gone away

This seems to happen consistently after approximately 39 seconds and 85.5 MB imported.

I read many suggestions on increasing max_allowed_packet in my.ini, so I did to 16M rather than the default of 1M. Yet the error I'm receiving persists no matter what I do. Any ideas?

Now solved in my.ini:

[wampmysqld]
port        = 3306
socket      = /tmp/mysql.sock
key_buffer = 384M
max_allowed_packet = 100M
table_cache = 4096
sort_buffer_size = 2M
net_buffer_length = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 64M
myisam_sort_buffer_size = 64M
basedir=c:/wamp/bin/mysql/mysql5.1.36
log-error=c:/wamp/logs/mysql.log
datadir=c:/wamp/bin/mysql/mysql5.1.36/data
thread_cache_size = 8
query_cache_size = 32M

innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 10M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100

wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 100M

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

Answer 1

What is the data structure in the sql?

It is possible one column is a blob, perhaps an image or PDF that is larger than 16M. Why not set the max_allowed_packet to 160MB (as no one packet can be bigger than the entire file size) and test it again, you can always switch it back later.

You can get a more accurate value if you get the data imported use the SHOW TABLE STATUS command to get the data sizes and you will then see how large it should be.

The error however is very general and there are a list of possible reasons you see the error on the mysql website

You will have to work through each one to locate the problem but in my experience it has always been a packet too big. I have max_packet_size set to 180MB on one of my production boxes for this very reason.

Answer 2

try to up the wait_timeout to like 100 in your my.cnf then restart the server

wait_timeout = 100

Answer 3

While Richard's answer is the common solution, check your .err log. It's possible MySQL is running out of memory and restarting. I've also had this recently happen with failing hardware.