Bug #50357 Innodb blob+text columns: empty strings become NULL after restore
Submitted: 15 Jan 2010 9:15 Modified: 1 Mar 2010 21:18
Reporter: Victor Kirkebo Email Updates:
Status: Patch queued Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0-backup OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: BLOB, innodb, null, restore, text

[15 Jan 2010 9:15] Victor Kirkebo
Description:
Also see bug#49414
Create an innodb table with blob and text columns. Insert empty strings. Do backup and restore. The empty strings have now changed to NULL values.

How to repeat:
mysql> create database mydb;
mysql> use mydb;

mysql> create table tb_innodb(f1 char(10), f2 varchar(10), f3 binary(10), f4 varbinary(10), f5 blob, f6 text) engine innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tb_innodb values('','','','','','');
Query OK, 1 row affected (0.02 sec)

mysql> select * from tb_innodb;
+------+------+------------+------+------+------+
| f1   | f2   | f3         | f4   | f5   | f6   |
+------+------+------------+------+------+------+
|      |      |            |      |      |      |
+------+------+------------+------+------+------+
1 row in set (0.00 sec)

mysql> backup database mydb to '/tmp/mydb.bck';
mysql> restore from '/tmp/mydb.bck' overwrite;

mysql> select * from tb_innodb;
+------+------+------------+------+------+------+
| f1   | f2   | f3         | f4   | f5   | f6   |
+------+------+------------+------+------+------+
|      |      |            |      | NULL | NULL |
+------+------+------------+------+------+------+
1 row in set (0.00 sec)
[15 Jan 2010 10:40] Sveta Smirnova
Thank you for the report.

Verified as described. Only repeatable with InnoDB storage engine.
[18 Jan 2010 20:19] Chuck Bell
Problem found. Solution requires saving the NULL status of blob fields.
[18 Jan 2010 20:36] 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/97301

2935 Chuck Bell	2010-01-18
      BUG#50357 : Innodb blob+text columns: empty strings become NULL after restore
      
      If an InnoDB table with zero length blob fields is backed up and
      restored, the fields are set to NULL instead of the empty string.
      
      This patch corrects the behavior by recording the NULL status
      during backup and applying it on restore.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for checking NULL status after restore for zero
        length blob fields.
     @ sql/backup/be_default.cc
        Added a check for saving NULL status during backup.
        Added a check to setting NULL status during restore.
     @ sql/backup/be_default.h
        Added status indicators.
[21 Jan 2010 6:01] Ritheesh Vedire
Not Approved. (Issue with the result file). Setting back to progress.
[22 Jan 2010 8:32] Ritheesh Vedire
Approved.
[22 Jan 2010 15:25] 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/97894

2936 Chuck Bell	2010-01-22 [merge]
      Local merge before push of BUG#50357
[20 Feb 2010 9:18] Bugs System
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100122221225-ccgkfpesvrttdlc1) (merge vers: 6.0.14-alpha) (pib:16)
[24 Feb 2010 10:43] Lars Thalmann
Seems this is not fixed.  Reopening bug report. /L

On Tue, Feb 23, 2010 at 06:17:44PM +0100, Victor Kirkebo wrote:
> I tested the latest 6.0.14-alpha today. The bugs I reported earlier
> were pushed into 6.0.14-alpha a couple of days ago. However, I'm
> still able to recreate one bug:
> http://bugs.mysql.com/bug.php?id=50357 (Innodb blob+text columns:
> empty strings become NULL after restore).
[1 Mar 2010 13:32] Chuck Bell
The patch was excluded from the trees. It was indeed pushed but somehow was removed during the back porting effort and/or recent merge fiasco.
[1 Mar 2010 21:06] 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/101954

3123 Chuck Bell	2010-03-01
      BUG#50357 : Innodb blob+text columns: empty strings become NULL after restore
      
      If an InnoDB table with zero length blob fields is backed up and
      restored, the fields are set to NULL instead of the empty string.
      
      This patch corrects the behavior by recording the NULL status
      during backup and applying it on restore.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for checking NULL status after restore for zero
        length blob fields.
     @ sql/backup/be_default.cc
        Added a check for saving NULL status during backup.
        Added a check to setting NULL status during restore.
     @ sql/backup/be_default.h
        Added status indicators.
[1 Mar 2010 21:18] Chuck Bell
Repushed patch unaltered.
[1 Mar 2010 21:32] 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/101957

3836 Chuck Bell	2010-03-01
      BUG#50357 : Innodb blob+text columns: empty strings become NULL after restore
      
      If an InnoDB table with zero length blob fields is backed up and
      restored, the fields are set to NULL instead of the empty string.
      
      This patch corrects the behavior by recording the NULL status
      during backup and applying it on restore.
      
      original changeset: 3132 (mysql-backup-backport)
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for checking NULL status after restore for zero
        length blob fields.
     @ sql/backup/be_default.cc
        Added a check for saving NULL status during backup.
        Added a check to setting NULL status during restore.
     @ sql/backup/be_default.h
        Added status indicators.