error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

Question

I'm trying to load mysqldump and I keep getting following error:

ERROR 2013 (HY000) at line X: Lost connection to MySQL server during query

/etc/my.cnf:

[mysqld]
max_allowed_packet = 16M
net_read_timeout = 30
net_write_timeout = 60
...
[mysqldump]
max_allowed_packet = 16M

I tried to increase these values, but I keep getting that error no matter what( What else can I do to overcome this error?

Answer 1

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.

Answer 2

The error code ERROR 2013 (HY000) related with aborted connection. You can run the following command to verify this.

mysql> SHOW GLOBAL STATUS LIKE  'Aborted_connects';

If the counter getting increased one by each attempt to connect, then it is an issue with connection.

One way to solve this issue, you can increase the connection timeout value in your configuration file. You can do that by using the following command.

mysql> SET GLOBAL connect_timeout = 10;

I hope this will help you. Thank you.

Answer 3

@A_funs was right, inspecting the system log yields this:

Aug 14 08:04:15 centos-php55 kernel: Killed process 8597 (mysqld) total-vm:7395680kB, anon-rss:3351108kB, file-rss:0kB, shmem-rss:0kB
Aug 14 08:04:15 centos-php55 mysqld_safe[7848]: /usr/bin/mysqld_safe: line 200:  8597 Killed                  LD_PRELOAD=/usr/lib64/libjemalloc.so.1 nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=1024000 --pid-file=/var/lib/mysql/mysql.pid --socket=/var/lib/mysql/mysql.sock < /dev/null > /dev/null 2>&1

So it might very well be possible you're (like me) running out of memory on the machine. My problem was that MySQL was using too much memory so the scheduler was killing the process. Actually lowering innodb_buffer_pool_size fixed the issue.

Answer 4

What command are you using to load mysqldump? Is this a production server? Size of the dump? Format of the dump (.gz or .sql)?

Check if the error caused due to restart,if yes 1) check mysql memory allocation 2) try to reduce memory allocation by reducing innodb_buffer_pool size

This will help to reduce swap usage.

Answer 5

I was using vagrant and had allocated just 768M of memory to the mysql -virtual machine. I increased the memory to 4096M and it worked again.