Bug #41486 extra character appears in BLOB for every ~40Mb after mysqldump/import
Submitted: 16 Dec 2008 2:24 Modified: 13 Apr 2009 19:21
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:5.0.51a, 5.0.67, 5.0.66sp1, 5.1 bzr OS:Linux (Ubuntu 8.04)
Assigned to: Alexey Kopytov CPU Architecture:Any
Tags: Contribution

[16 Dec 2008 2:24] Andrii Nikitin
Description:
mysqldump/import of big BLOB's brings new characters on Ubuntu

How to repeat:
1. Create test data
create table test.testbigblob(data longblob);

insert into test.testbigblob 
select repeat('1',30000000) 
union
select repeat('1',40000000) 
union
select repeat('1',80000000) 
union
select repeat('1',120000000);

2. Dump/reload
mysqldump -uroot -p1 test testbigblob > ~/testbigblob.sql
mysql -uroot -p1 test < ~/testbigblob.sql

3. See new data size:
mysql> select length(data) from test.testbigblob;
+--------------+
| length(data) |
+--------------+
|     30000000 | 
|     40000001 | 
|     80000002 | 
|    120000003 | 
+--------------+

seem with every ~40Mb of data additional character appears

Suggested fix:
let binary data be consistent.
[16 Dec 2008 2:28] Andrii Nikitin
woraround: -use mysqldump with -T option
[15 Jan 2009 22:11] Sveta Smirnova
Bug #42115 was marked as duplicate of this one.

Verified with version 5.1 also: bug exists.
[20 Jan 2009 14:01] Sveta Smirnova
Same with BIGTEXT column.
[20 Jan 2009 17:21] Sveta Smirnova
Tested dump file: mysqldump is not issue, data is corrupting while loding using mysql command line client. Another workaround: use MySQL Administrator to load dump.
[21 Jan 2009 21:49] Sveta Smirnova
Patch for this bug

Attachment: bug41486.diff.v3 (application/octet-stream, text), 3.09 KiB.

[5 Mar 2009 16:47] Alexey Kopytov
Another workaround: when importing the dump file, set max_allowed_packet for the 'mysql' command so that it is big enough to hold the largest string from the input file. E.g. for the bug's test case running the following to import the dump produces correct results:

mysql -uroot --max_allowed_packet=130M test < testbigblob.sql
[18 Mar 2009 8:19] 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/commits/69536

2764 Alexey Kopytov	2009-03-18
      Fix for bug#41486: extra character appears in BLOB for every 
                         ~40Mb after mysqldump/import 
              
      When the input string exceeds the maximum allowed size for the 
      internal buffer, batch_readline() returns a truncated string. 
      Since there was no way for a caller to determine whether the 
      string was truncated or not, the command line client assumed 
      batch_readline() to always return the whole input string and 
      appended a newline character. This resulted in garbled data 
      when importing dumps containing strings longer than the 
      maximum input buffer size. 
        
      Fixed by adding a flag to the batch_readline() interface to 
      signal a truncated string to the caller. 
        
      Other minor problems fixed during patch implementation: 
       
      - The maximum allowed buffer size for batch_readline() was set 
      up depending on the client's max_allowed_packet value. It does 
      not actully make any sense, as those variables are not 
      related. The input buffer size limit is now always set to 1 
      MB. 
        
      - fill_buffer() did not always set the EOF flag. 
       
      - The input buffer could actually grow twice as the specified 
      limit due to insufficient checks in intern_read_line(). 
      modified:
        client/my_readline.h
        client/mysql.cc
        client/readline.cc
        mysql-test/r/mysql.result
        mysql-test/t/mysql.test
[19 Mar 2009 10:03] 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/commits/69727

2765 Alexey Kopytov	2009-03-19
      Fixed test failures in 5.1/6.0 introduced by the patch
      for bug #41486. Session max_allowed_packet is read-only
      as of MySQL 5.1.31. In addition, the global variable
      now has no effect on the current session.
      modified:
        mysql-test/r/mysql.result
        mysql-test/t/mysql.test
[25 Mar 2009 8: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/commits/70313

2839 Alexey Kopytov	2009-03-25
      Disabled embedded server in the test case for bug #41486.
      modified:
        mysql-test/t/mysql-bug41486.test
[27 Mar 2009 14:32] Bugs System
Pushed into 5.0.80 (revid:joro@sun.com-20090327142516-55gumdxj39z6eijj) (version source revid:leonard@mysql.com-20090324072904-7w2lxdxzw8hx1rnm) (merge vers: 5.0.80) (pib:6)
[27 Mar 2009 14:57] Bugs System
Pushed into 5.1.34 (revid:joro@sun.com-20090327143448-wuuuycetc562ty6o) (version source revid:zhou.li@sun.com-20090325093729-7afsyb8lqoyp9cly) (merge vers: 5.1.34) (pib:6)
[3 Apr 2009 2:54] Paul DuBois
Noted in 5.0.80, 5.1.34 changelogs.

The mysql client could misinterpret its input if a line was longer 
than an internal buffer.

Setting report to NDI pending push into 6.0.x.
[13 Apr 2009 9:20] Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090413084402-snnrocwzktcl88ny) (version source revid:zhou.li@sun.com-20090325090450-zkyukat1zh39nytm) (merge vers: 6.0.11-alpha) (pib:6)
[13 Apr 2009 19:21] Paul DuBois
Noted in 6.0.11 changelog.
[9 May 2009 16:41] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (merge vers: 5.1.34-ndb-6.2.18) (pib:6)
[9 May 2009 17:38] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (merge vers: 5.1.34-ndb-6.3.25) (pib:6)
[9 May 2009 18:36] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (merge vers: 5.1.34-ndb-7.0.6) (pib:6)