'
Uncategorized

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

author
4 minutes, 7 seconds Read

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?

Set the value in the [mysql] section of the config, as well? (Not [mysqladmin] though). SELECT @@MAX_ALLOWED_PACKET; to confirm that the server is using these values? 

– Michael – sqlbotJan 1, 2016 at 4:15

@Michael-sqlbot I did typo, I meant to write mysqldump (not mysqldadmin), should I just keep max_allowed_packet or add net_read_timeout & net_write_timeout as well? 

– alexusJan 1, 2016 at 21:27

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.

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.

right after I got error, I checked for Aborted_connects and it shows me 0. 

– alexusJan 2, 2016 at 5:45

Mine was close, but this gave me the hint: I needed to check for Aborted_clients instead, but this worked as I checked by using "SHOW GLOBAL STATUS LIKE '%Aborted%';" which by coincidence showed both Aborted values. 

– TheSatinKnightJan 16, 2021 at 16:02

@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.

Thanks for the contribution. How does it differ from previous answers? Because OP stated I’m not running out of memory and my innodb_buffer_pool_size IS set to 80%. 

– John K. N.Aug 14, 2018 at 8:13

Oh, I didn’t notice OPs answer. Oh well, might as well leave it here. 

– adioe3Aug 14, 2018 at 8:17

I agree with @hot2use that your answer as posted isn’t really adding much new value, and given how much later you’ve posted it, it might also be a little confusing as it kind of invalidates the OP’s comment (which has been there for a long time too) saying that theirs wasn’t a memory issue. On the other hand, if the innodb_buffer_pool_size value that fixed the problem for you had to be lower than the recommended 80%, maybe you could add that to your answer (specifying the actual value you set it to), cheers. 

– Andriy MAug 14, 2018 at 13:09

I down-vote this answer as that’s a copy of someone else already wrong answer. 

– alexusAug 14, 2018 at 13:16

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.

Yes. Just reduced innodb_buffer_pool_size from 4GB to 3GB , and fixed. 

– Joilson CardosoAug 13, 2018 at 16:26

source : https://dba.stackexchange.com/questions/124964/error-2013-hy000-lost-connection-to-mysql-server-during-query-while-load-of-my

Similar Posts