MySQL Forums
Forum List  »  Backup

mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
Posted by: B P
Date: January 07, 2016 05:02AM

Hello,

I have the following problem to backup / dump a large table on my Database :

test01 ~ # mysqldump -u root -p -v MyDB > test.sql
Enter password:
-- Connecting to localhost...
-- Retrieving table structure for table MyTable0...
-- Sending SELECT query...
-- Retrieving rows...
-- Retrieving table structure for table MyTable1...
-- Sending SELECT query...
-- Retrieving rows...
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `MyTable1` at row: 18132197


As I tried the following things to try to fix this issue

- Increase the net_write_time / net_read_time value :

mysql> SET GLOBAL net_write_timeout=3600;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL net_read_timeout=3600;
Query OK, 0 rows affected (0.00 sec)


I have also tried to dump my DB with the following mysqldump parameters :


- mysqldump -u root -p MyDB | gzip -c > MyDB.sql.gz
- mysqldump -u root -p MyDB > MyDB.sql
- mysqldump -u root -p -q --single-transaction -v MyDB > MyDB.sql

- mysqldump -u root -p --max_allowed_packet=500m --net_buffer_length=16m -q --single-transaction -v MyDB > MyDB.sql

- mysqldump -u root -p --max_allowed_packet=2147483648 --net_buffer_length=16777216 -v MyDB > MyDB.sql
- mysqldump -u root -p --max_allowed_packet=2147483648 --net_buffer_length=16777216 -q --single-transaction -v MyDB > MyDB.sql


Note I add the following lines to my.cfg :

wait_timeoued_timeout=28800
interactive_timeout = 28800
net_read_timeout =3600
net_write_timeout =3600

But only the parameter interactive_timeout seems modified :

test01 ~ # mysqladmin -u root -p variables | grep _timeout
Enter password:
| connect_timeout | 10
| delayed_insert_timeout | 300
| innodb_lock_wait_timeout | 50
| innodb_rollback_on_timeout | OFF
| interactive_timeout | 28800
| lock_wait_timeout | 31536000
| net_read_timeout | 30
| net_write_timeout | 60
| slave_net_timeout | 3600
| wait_timeout | 28800



So I started manually mysqld using the following cmd and cross check with mysqladmin :

test01 mysql # /usr/sbin/mysqld --net_read_timeout=3600 --net_write_timeout=3600 --wait_timeout=28800
160107 11:50:40 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
160107 11:50:40 [Note] /usr/sbin/mysqld (mysqld 5.5.46-0ubuntu0.14.04.2) starting as process 4366 ...

Check the timeout values :
| connect_timeout | 10
| delayed_insert_timeout | 300
| innodb_lock_wait_timeout | 50
| innodb_rollback_on_timeout | OFF
| interactive_timeout | 28800
| lock_wait_timeout | 31536000
| net_read_timeout | 3600
| net_write_timeout | 3600
| slave_net_timeout | 3600
| wait_timeout | 28800




test01 ~ # mysqldump -u root -p MyDB | gzip -c > MyDB.sql.gz
Enter password:
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Mytable1` at row: 18132197

Finally, I got the same issue

Note also that :

mysql> select count(id_Mytable01) from Mytable01 ;
+------------------+
| count(Mytable01) |
+------------------+
| 9299300 |
+------------------+
1 row in set (2.30 sec)

mysql> select count(Mytable02) from Mytable02 ;
ERROR 2013 (HY000): Lost connection to MySQL server during query


Additionnal information :


test01 mysql # uname -a
Linux test01 3.16.0-30-generic #40~14.04.1-Ubuntu SMP Thu Jan 15 17:43:14 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

test01 mysql # mysqld --version
mysqld Ver 5.5.46-0ubuntu0.14.04.2 for debian-linux-gnu on x86_64 ((Ubuntu))

test01 ~ # free -m
total used free shared buffers cached
Mem: 32066 5221 26844 59 27 4186
-/+ buffers/cache: 1006 31059
Swap: 0 0 0


Any suggestion ?

Options: ReplyQuote


Subject
Views
Written By
Posted
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table
38061
B P
January 07, 2016 05:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.