6

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 - sqlbot Jan 1 '16 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? – alexus Jan 1 '16 at 21:27
4

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.

  • 2
    I'm not running out of memory and my innodb_buffer_pool_size IS set to 80%. – alexus Jan 5 '17 at 20:58
  • 1
    how to check on RDS AUrora – Volatil3 Aug 15 '18 at 14:38
0

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.

  • 2
    right after I got error, I checked for Aborted_connects and it shows me 0. – alexus Jan 2 '16 at 5:45
0

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 Cardoso Aug 13 '18 at 16:26
-2

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

  • 2
    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%. – hot2use Aug 14 '18 at 8:13
  • 1
    Oh, I didn't notice OPs answer. Oh well, might as well leave it here. – adioe3 Aug 14 '18 at 8:17
  • 2
    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 M Aug 14 '18 at 13:09
  • I down-vote this answer as that's a copy of someone else already wrong answer. – alexus Aug 14 '18 at 13:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.