MySQL Bugs: #44953: Error 2013: Lost connection to MySQL server during query when dumping table NFS

Bug #44953	Error 2013: Lost connection to MySQL server during query when dumping table NFS
Submitted:	19 May 2009 13:19	Modified:	19 Oct 2011 22:02
Reporter:	Guy Baconniere	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	4.1.11,5.1.34	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	big table, compatible mysql40, dump table, ERROR 2013, Latin1, Lost connection, mysqldump, rt ticket, utf8

Description:
We use a latin1 InnoDB table to store email attachments. 
This table contains 1.5 mio rows for 18 GB of data. 

We can only dump it successfully when we use the mysqldump 
default options (--opt etc) but we cannot dump it when 
using --default-character-set=latin1 or --compatible=mysql40.

We tried to increase --max-allowed-packet up to 1G on both
server and client side and use --quick but we got always 
the same error after a random period of time :

<</usr/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Attachments` at row: 842358 >>

<</usr/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Attachments` at row: 532750 >>

...

We need to dump it using latin1 or mysql40 compatiblity (latin1)
otherwise all utf8 characters stored in latin1 are lost.
This seems related to another bugs :
http://bugs.mysql.com/bug.php?id=11612
http://bugs.mysql.com/bug.php?id=26557
http://bugs.mysql.com/bug.php?id=30162

N.B. Some attachments contains utf8 characters but the
current version of the application (RT 3.4.CHALDEA)
was designed to run on MySQL 4.0 and it cannot handle
table in utf8 in the version we are running.

We know MySQL 4.1.11 is quite old but we need to be able
to dump it to move to the last release of MySQL 5.0 on 64-bit OS 
with splitted tablespace InnoDB etc.

How to repeat:
1) Install mysql 4.1.11

2)
 
CREATE TABLE `Attachments` (
  `id` int(11) NOT NULL auto_increment,
  `TransactionId` int(11) NOT NULL default '0',
  `Parent` int(11) NOT NULL default '0',
  `MessageId` varchar(160) default NULL,
  `Subject` varchar(255) default NULL,
  `Filename` varchar(255) default NULL,
  `ContentType` varchar(80) default NULL,
  `ContentEncoding` varchar(80) default NULL,
  `Content` longtext,
  `Headers` longtext,
  `Creator` int(11) NOT NULL default '0',
  `Created` datetime default NULL,
  PRIMARY KEY  (`id`),
  KEY `Attachments1` (`Parent`),
  KEY `Attachments2` (`TransactionId`),
  KEY `Attachments3` (`Parent`,`TransactionId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 MAX_ROWS=10000000 AVG_ROW_LENGTH=4064;

3) You can take any email with special character like greek, russian encoded in utf8 and split it into Headers and Content as is. Fill the table will 1.5 mio rows (around 18 GB of data)

4) mysqldump this table or all database using

mysqldump --force --single-transaction --flush-logs --master-data=1 --add-drop-table --skip-add-locks --create-options --quick --extended-insert --skip-lock-tables --set-charset --default-character-set=latin1 --disable-keys --quote-names --allow-keywords --max_allowed_packet=512M --databases rt341 >/var/backups/mysql/mysql.dump 2>/var/backups/mysql/mysqldump.log

mysqldump --single-transaction --compatible=mysql40 --add-drop-table --skip-add-locks --create-options --quick --extended-insert --skip-lock-tables --disable-keys --quote-names --allow-keywords --max-allowed-packet=64M --databases rt341 >/var/backups/mysql/mysql.dump 2>/var/backups/mysql/mysqldump.log

mysqldump --compatible=mysql40 --max-allowed-packet=64M --create-options --extended-insert --quote-names --add-drop-table --allow-keywords --databases rt341 >/var/backups/mysql.dump 2>/var/backups/mysql/mysqldump.log

mysqldump --compatible=mysql40 --max-allowed-packet=1000M --create-options --extended-insert --quote-names --add-drop-table --allow-keywords --databases rt341 >/var/backups/mysql.dump 2>/var/backups/mysql/mysqldump.log

5) All mysqldump commands will fail with 

/usr/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Attachments` at row: xxxxxx

Thank you for the problem report. Please, send your my.cnf file content, the results of SHOW TABLE STATUS for the table and

free
uname -a

commands from OS.

MySQL Configuration

Attachment: rt_my.cnf (application/octet-stream, text), 2.96 KiB.

> Thank you for the problem report. Please, send your my.cnf file content, the results of

in attachment

> SHOW TABLE STATUS for the table and

mysql -B -e "SHOW TABLE STATUS LIKE 'Attachments'\G" rt341
*************************** 1. row ***************************
           Name: Attachments
         Engine: InnoDB
        Version: 9
     Row_format: Dynamic
           Rows: 8344345
 Avg_row_length: 2284
    Data_length: 19058917376
Max_data_length: NULL
   Index_length: 179159040
      Data_free: 0
 Auto_increment: 3747188
    Create_time: 2008-12-14 14:26:09
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: max_rows=10000000 avg_row_length=4064
        Comment: InnoDB free: 1751040 kB

> free

free -m
             total       used       free     shared    buffers     cached
Mem:          3801       2269       1532          0        126        196
-/+ buffers/cache:       1946       1855
Swap:          956         18        938

> uname -a

Linux rt 2.6.10 #1 SMP Mon Mar 21 09:44:20 GMT 2005 i686 GNU/Linux

Additional Infos:

cat /etc/debian_version
3.1

dpkg -l mysql-server-4.1 | grep mysql
ii  mysql-server-4 4.1.11a-4sarge mysql database server binaries

mysql -B -N -e 'SELECT version()'
4.1.11-Debian_4sarge2-log

cat /proc/cpuinfo | grep -E '(processor|model name)'
processor       : 0
model name      : Intel(R) Xeon(TM) CPU 2.40GHz
processor       : 1
model name      : Intel(R) Xeon(TM) CPU 2.40GHz
processor       : 2
model name      : Intel(R) Xeon(TM) CPU 2.40GHz
processor       : 3
model name      : Intel(R) Xeon(TM) CPU 2.40GHz

mount | grep "/var "
/dev/sda5 on /var type ext3 (rw)

I can also confirm the error can be repeated with a simpler command :

mysqldump --compatible=mysql40 --result-file=mysql.dump --databases rt341

/usr/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Attachments` at row: 358957

Next step for me is to plan to do cold backup of all Innodb ib* files and move them to a VMware guest with same OS, Kernel, MySQL version.

Then I can try 
* change kernel to 2.6.8 Debian and last 2.6.x (we a running a custom kernel 2.6.10 instead of 2.6.8 on Debian Sarge)
* tune my.cnf
* upgrade to last 4.1.x, to last 5.0.x

mysqldump --set-charset --default-character-set=latin1 -hmysql4111 rt341 ABacoTickets

Attachment: ABacoTickets-latin1.sql (text/x-sql), 3.50 KiB.

mysqldump --set-charset --compatibile=mysql40 -hmysql4111 rt341 ABacoTickets

Attachment: ABacoTickets-mysql40.sql (text/x-sql), 2.95 KiB.

mysqldump --skip-lock-tables --set-charset -hmysql4111 rt341 ABacoTickets

Attachment: ABacoTickets-utf8.sql (text/x-sql), 3.52 KiB.

mysqldump --skip-lock-tables --set-charset -hmysql5051a rt341 ABacoTickets

Attachment: ABacoTickets-utf8-rtdb.sql (text/x-sql), 3.54 KiB.

> We need to dump it using latin1 or mysql40 compatiblity (latin1)
> otherwise all utf8 characters stored in latin1 are lost.
> This seems related to another bugs :
> http://bugs.mysql.com/bug.php?id=11612
> http://bugs.mysql.com/bug.php?id=26557
> http://bugs.mysql.com/bug.php?id=30162

(1)

Attachment: ABacoTickets-latin1.sql
mysqldump --set-charset --default-character-set=latin1 -hmysql4111 rt341 ABacoTickets

BEST on a small table but failed on a 18G Attachments table 

Dumping MySQL 4.1.11 with latin1 default character set preserve 
the utf8 within latin1 table and create a latin1 1:1 (same MD5 for Subject)
after the restoration on MySQL 5.0.51a

(2)

Attachment: ABacoTickets-mysql40.sql
mysqldump --set-charset --compatible=mysql40 -hmysql4111 rt341 ABacoTickets

GOOD but failed on a 18G Attachments table and create a UTF8 table if default character set of DB is UTF8

Dumping MySQL 4.1.11 with compatible mysql40 preserve the utf8 
within latin1 table but create a UTF8 on the target (not the same MD5 for Subject) after the restoration on MySQL 5.0.51a

(3)

Attachment: ABacoTickets-utf8.sql
mysqldump --skip-lock-tables --set-charset -hmysql4111 rt341 ABacoTickets

(REALLY) BAD some characters are replaced by two after restoration but this solution is able to backup a 18G Attachments table successfully on MySQL 4.1.11

Dumping MySQL 4.1.11 with default opt (equal to default character utf8)
DONT preserve the utf8 within latin1 table (not the same MD5 for Subject) after the restoration on MySQL 5.0.51a. -- The worst

(4)

Attachment: ABacoTickets-utf8-rtdb.sql
mysqldump --skip-lock-tables --set-charset -hmysql5051a  rt341 ABacoTickets

OK same mysqldump command as (3) but on a MySQL 5.0.51a instead of 4.1.11

Dumping MySQL 5.0.51a with default opt (equal to default character utf8)
preserve the utf8 within latin1 table (the same MD5 for Subject) after the restoration on MySQL 5.0.51a.

Questions:

* Why solution (1 --latin1) on MySQL 4.1.11 is working with a small table (with utf8 stored on latin1) but not on a 18G table and result with <<mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `...` at row: ...>>

* Why solution (3 -- utf8) on MySQL 4.1.11 corrupt some characters and not on MySQL 5.0.51a ? (a patched bug ?)

* Why solution (3 -- utf8) on MySQL 4.1.11 is able to dump 18G table successfully (without any Error 2013) ?

Also failed with --default-character-set=binary 
(if you remove --default-character-set it works)

mysqldump --force --single-transaction --flush-logs --master-data=1 --add-drop-table --create-options --quick --extended-insert --skip-lock-tables --set-charset --default-character-set=binary --disable-keys --quote-names --allow-keywords --max-allowed-packet=64M --result-file=/var/backups/mysql/mysql.dump --databases rt341 2>/var/backups/mysql/mysqldump.log

/usr/bin/mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `Attachments` at row: 1028424

Thank you for the feedback.

Have you tried to:

1. Dump tables in a way which 4.1.11 allows to do
2. Restore into current version 5.0.81 or 5.1.34
3. Dump with required --compatible option

If not, please try and inform us about results.

> Have you tried to:
>
> 1. Dump tables in a way which 4.1.11 allows to do
> 2. Restore into current version 5.0.81 or 5.1.34
> 3. Dump with required --compatible option
>
> If not, please try and inform us about results.

I have only tried dumping on 4.1.11 using utf8 then
restore on 5.0.51a. It works but some utf8 characters
stored on latin1 are lost or replaced by double unknown 
characters.

If you dump using utf8 on 4.1.11 then upgrade MySQL
to 5.0.51a and then dump it again you can see
the diff between ABacoTickets-utf8.sql (4.11) and 
ABacoTickets-utf8-rtdb.sql (5.0.51a) and the strange
double characters I am talking about.

> http://forums.mysql.com/read.php?28,201159,201159
> http://jeremy.zawodny.com/blog/archives/000690.html
> http://forums.zmanda.com/archive/index.php/t-1410.html

I am also dumping MySQL DB to a NFS volume (NetApp)
Maybe MySQL Error 2013 is linked to this as well.

NFS is mounted using
172.16.xx.xx:/vol/vol_rt /var/backups nfs rw,bg,hard,rsize=32768,wsize=32768,vers=3,nointr,timeo=600,proto=tcp 0 2

I will do some additional tests by backuping on local filesystem.

My dumping problem is solved.

The workaround to wrong characters (or duplicate characters) when using mysqldump default character set (UTF8) to dump latin1 table who has russian, greek, japanese characters in it is to add --default-character-set=binary (or latin1) to mysqldump.

The workaround to "Error 2013: Lost connection to MySQL server during query when dumping table" in my case was to mysqldump to local filesystem and not on NFS. 

This problem can only be reproduced by dumping a really big table (18 GB) on NFS under heavy load (continuous insertions on the table when the dump it)

I still don't understand why mysqldump always work on a NFS volume with --default-character-set=utf8 and not with --default-character-set=binary nor latin1. This is very weird. Maybe MySQL handle buffer differently when changing default character-set in the code (buffer underrun etc).

You can close this ticket if you want.

Thank you for the feedback.

I close the report as "Unsupported", because we don't support backport to old versions. If you are able to repeat the problem with newer version of mysqldump/mysqld fill free to reopen the report.

Regarding to --default-character-set=utf8 this is default option which can be overloaded.

I have found the same bug on version 5.1.34. If I re-direct mysqldump's output to a local volume, the backup works just fine. If I re-direct mysqldump's output to an NFS volume (directly or piping through gzip) it fails as described. No other options change.

Could the bug be re-opened and looked into?

I am suspecting something is timing out while writing to the NFS volume on the client side, which in turn causes the server to drop the connection.

Verifying as D3. Workaround is to dump to non-NFS volume.

Most likely, this is not a bug. Simply, it takes too long time to write to NFS over the network, so server runs out of net_write_timeout period.

This is expected situation and a solution is to increase this timeout on the server, for the session. This can be done either by setting session value from mysql CLI or from mysqldump itself.

Guy,

please try if increasing net_write_timeout works for you.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Note, one cannot set session net_write_timeout with mysqldump , see http://bugs.mysql.com/bug.php?id=47311

Shannon,

one still can use SET GLOBAL

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".