MySQL Bugs: #41000: MySQLdump writes erronous INSERT statements

Bug #41000	MySQLdump writes erronous INSERT statements
Submitted:	25 Nov 2008 7:09	Modified:	27 Dec 2008 7:27
Reporter:	Robin Diederen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (Any)
Assigned to:		CPU Architecture:	Any
Tags:	mysqldump 1064

Description:
Hello,

We use the mysqldump tool to backup lots of databases. Some of these databases are quite large (>200Gb). Recently we started testing the integrity of database backups and we found that quite a few cannot be re-imported into MySQL. They all fail with an "1064" error (see below for example).

The strange this is that these dumps have been made on the same or (nearly) identical MySQL servers (including their configuration). 

While I am not sure, I somehow expect that mysqldump has some quotation problems.

See here:
ERROR 1064 (42000) at line 1377673: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''%PDF-1.3\n%??\n8 0 obj\n<</Length 9 0 R/Filter /FlateDecode>>\nstream\nx????' at line 1

How to repeat:
Dump the same database, try an import, same thing happens.

Thank you for a problem report. Please, check if mysqldump/mysql clients from a newer version, 5.0.67, will work for you. if it will not, please, send your my.cnf file content from the system where you try to restore the dump.

Hi,

I'll try the 5.0.67 binary. However, I don't think that will do any good, as the version of mysqldump is the same (both version 10.11). 

My my.cnf on the target system looks as follows:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

old_passwords=1
server-id=2

max_allowed_packet=16M

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 3600

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Best, Robin

Thank you for the report.

But version 5.0.45 is old. Please try with current version 5.0.67 and if problem still exists experiment with max_allowed_packet: increase its value.

Hi,

Are there any differences between mysqldump versions when their version numbers are identical?

I'll be doing some tests with a more recent version of mysqldump. Would replacing the mysqldump binary (and nothing else) be sufficient? 

Best, Robin

Robin,

I think main problem is not mysqldump itself, but value of max_allowed_packet which just crops queries from the dump. (== not a bug)

In other side we don't backport bug fixes. So if this is rare case when problem is not size of max_allowed_packet, but real bug we need to know if problem repeatable with latest version. This is why I asked you to upgrade (and server as well, because is not known if problem with mysqldump or mysqld).

Hi Sveta,

I just gave one of the problematic dumps a try with a MySQL server configured to do a max_allowed_packet_size of 64MB.. still no go. 

I'll give the new version a try (could take a few days...).

Best, Robin

Thank you for the answer.

We will wait result from 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".