mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table

[B P]

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 ?