Bug #9756 mysql client failing on dumps containing certain \ sequences
Submitted: 8 Apr 2005 13:18 Modified: 26 Apr 2005 16:16
Reporter: Axel Schwenke Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:5.0.4 OS:Linux (Linux)
Assigned to: Ramil Kalimullin

[8 Apr 2005 13:18] Axel Schwenke
Description:
The command line client sometimes failes with error message
  Unknown command '\''
when reading data from a dump containing escaped ' characters.
The problem seems to be connected with buffer sizes, as it can be triggered by fooling mysql with max_allowed_packet. However this problem also arises when replaying a 2MB dump with 1MB INSERT statements using mysql client with max_allowed_packet=16M.

How to repeat:
#create a suitable table
mysql> create table bar (x char(10));

#insert a few (say: 1 million ;-) rows containing the string "' '" (single quote + space + single quote)

mysql> show table status like 'bar'\G
*************************** 1. row ***************************
           Name: bar
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 1000000
 Avg_row_length: 11
    Data_length: 11000000
Max_data_length: 47244640255
   Index_length: 1024
...

#now dump this table and read it back

~/Work/MySQL $mysqldump test bar >bar.dump 
~/Work/MySQL $mysql test <bar.dump 

--> everything fine so far

#have a look at the created dump
~/Work/MySQL $fgrep INSERT bar.dump | wc
     11 1000055 10000286
~/Work/MySQL $for i in `seq 11`; do fgrep INSERT bar.dump | head -$i | tail -1 | wc; done
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   95234  952316
      1   47715  477126
~/Work/MySQL $head -31 bar.dump | tail -1
LOCK TABLES `bar` WRITE;
~/Work/MySQL $head -43 bar.dump | tail -1
UNLOCK TABLES;

--> we got 11 INSERT statements in lines 32..42, each except the last ~1M in size

#now we fool the mysql client about max_allowed_packet

~/Work/MySQL $mysql --max_allowed_packet=1M test <bar.dump  
~/Work/MySQL $mysql --max_allowed_packet=512k test <bar.dump
~/Work/MySQL $mysql --max_allowed_packet=508k test <bar.dump
ERROR at line 42: Unknown command '\''.

--> Kaboom!

Questions:

1. Why do I get an "Unknown command" error and not something like "buffer overrun"?
2. Why do I get an error at max_allowed_packet=508KB but not at 512KB? Lines in the dump are definitely bigger than 512k.
3. Why do I get this error at line 42, that contains the last (shorter!) INSERT statement?
4. After the failed restore, the table contains 476145 rows. Seems only the first 5 out of 10 identical INSERTs succeeded. What about number 6..10? 

Again: I just tried to create a test case for a bug that hits me with a 2MB dump, containing 2 INSERT statements of ~1MB each. There is nothing special about the table or the dump (it just contains a lot of escaped "' '" strings). max_allowed_packet is 512MB for the server and 16MB for mysql client.
[25 Apr 2005 15:10] Axel Schwenke
I added an SQL dump to trigger the bug. This dump was done with --net_buffer_length=4KB. The command line client fails on line #503 - BUT: there is no \' sequence in this line.  There are several of those before (the last one in line #498) and others later (next in line #508) in the dump.
[26 Apr 2005 10:43] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24307
[26 Apr 2005 10:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/24308
[26 Apr 2005 15:27] Ramil Kalimullin
fixed in 5.0.6
[26 Apr 2005 16:16] Paul Dubois
Noted in 5.0.6 changelog.
[8 Nov 2007 16:00] Christian Kirsch
I just stumbled upon the same (or a similar?) bug trying to restore a dump from a Linux machine running 5.0.33 (compiled from source) on an Intel-Mac running 5.0.45 (precompiled from MySQL, PKG format). Using a max-allowed-packet of 16M, I get ERROR at line 903: Unknown command '\''.
If I set the max-allowed-packet to 1M, I get ERROR at line 906: Unknown command '\''. (The error now seems to be three lines further down). Setting the packet size to 512k, I see ERROR at line 121: Unknown command '\0'.
-- again, another line number and another offending character this time. The error happens if I pipe the output of mysqldump directly into mysql or if I first save it in a file and then feed that file to mysql.
[8 Aug 2008 11:48] Thomas Witt
I can second that. Same problem when trying to go with a large mysqldump
from 5.0.26 to 5.0.51:

  ERROR at line 2248: Unknown command '\0'.

We will now try to destore the whole file databasewise.
[13 May 2009 8:16] Colin Draper
I have exactly this issue when using 5.1.34 a on windows platform.

It is the database used for Mantis bug tracking, the maximum upload size is 5M
The dump file looks to be correct with escape sequences seem to be correctly placed, however trying to recover the dump always fails on the same statement.

Setting the max_allowed_packet to 10M still does not solve the problem. The dump file is about 7G and the table being reloaded is the bug_file_table which contains a longblob type (file content) hence the large size. 

I have had to ditch mysqldump as a backup methodology as it is not reliable to recover the database.
[5 Jul 2011 9:26] Vlad Safronov
got the same error on 5.1.35, Linux.