Bug #49414 Innodb tbl w/blob and text column with NULL no longer NULL after backup/restore
Submitted: 3 Dec 2009 19:34 Modified: 2 Mar 2010 1:28
Reporter: Victor Kirkebo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:6.0-backup, 6.0.14 OS:Any
Assigned to: Chuck Bell CPU Architecture:Any
Tags: BLOB, innodb, null, restore, text
Triage: Triaged: D2 (Serious)

[3 Dec 2009 19:34] Victor Kirkebo
Description:
Create an innodb table with blob and text columns. Insert NULL values. Do backup and restore. The NULL values have now changed to empty strings.

How to repeat:
mysql> create database mydb;
mysql> use mydb;
mysql> create table mytbl1(f1 char(255), f2 varchar(1024), f3 binary(255), f4 varbinary(1024), f5 blob, f6 text);
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into mytbl1 values(NULL,NULL,NULL,NULL,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

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

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

mysql> select * from mytbl2;
+------+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   | f6   |
+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+
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 mytbl1;
+------+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   | f6   |
+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

mysql> select * from mytbl2;
+------+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   | f6   |
+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL |      |      |
+------+------+------+------+------+------+
1 row in set (0.01 sec)
[3 Dec 2009 19:43] Valeriy Kravchuk
Verified just as described with recent 6.0.14 from mysql-6.0-codebase tree:

77-52-7-73:6.0-codebase openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.14-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database mydb;
Query OK, 1 row affected (0.00 sec)

mysql> use mydb;
Database changed
mysql> create table mytbl1(f1 char(255), f2 varchar(1024), f3 binary(255), f4
    -> varbinary(1024), f5 blob, f6 text);
Query OK, 0 rows affected (0.06 sec)

mysql> create table mytbl2(f1 char(255), f2 varchar(1024), f3 binary(255), f4
    -> varbinary(1024), f5 blob, f6 text) engine innodb;
Query OK, 0 rows affected (0.20 sec)

mysql> 
mysql> insert into mytbl1 values(NULL,NULL,NULL,NULL,NULL,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> insert into mytbl2 values(NULL,NULL,NULL,NULL,NULL,NULL);
Query OK, 1 row affected (0.01 sec)

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

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

mysql> 
mysql> backup database mydb to '/tmp/mydb.bck';
+-----------+
| backup_id |
+-----------+
| 5776      |
+-----------+
1 row in set (0.90 sec)

mysql> restore from '/tmp/mydb.bck' overwrite;
+-----------+
| backup_id |
+-----------+
| 5777      |
+-----------+
1 row in set (0.28 sec)

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

mysql> select * from mytbl2;
+------+------+------+------+------+------+
| f1   | f2   | f3   | f4   | f5   | f6   |
+------+------+------+------+------+------+
| NULL | NULL | NULL | NULL |      |      |
+------+------+------+------+------+------+
1 row in set (0.01 sec)
[9 Dec 2009 19:54] Chuck Bell
Chuck steals the bug...may return it if my intuition about the problem is incorrect (I think it is a simple fix).
[9 Dec 2009 19:58] Chuck Bell
Chuck steals the bug...may return it if my intuition about the problem is incorrect (I think it is a simple fix).
[9 Dec 2009 21:04] Chuck Bell
The problem was a simple issue. The backup default driver was writing blobs even if there was no data then calling a method to set it as not null. Rather, the code should check the size of the blob data read from the backup image and only write (and set not null) when there is data to write.
[9 Dec 2009 21:17] 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/93397

2904 Chuck Bell	2009-12-09
      BUG#49414 : Innodb tbl w/blob and text column with NULL no 
                  longer NULL after backup/restore
      
      The backup default driver was writing blobs even if there
      was no data then calling a method to set it as not null. 
      This set the fields as not null and wrinting an empty string
      to the field.
      
      This patch changes the restore driver so that it checks the
      size of the blob data read from the backup image and only 
      writes to the field (and set not null) when there is data 
      to write.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for NULL blob fields.
     @ sql/backup/be_default.cc
        Changed to include check if there is data to write.
[10 Dec 2009 13:18] Rafal Somla
Good to push.
[11 Dec 2009 10:09] Ritheesh Vedire
APPROVED.
[11 Dec 2009 13:20] 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/93683

2904 Chuck Bell	2009-12-11
      BUG#49414 : Innodb tbl w/blob and text column with NULL no 
                  longer NULL after backup/restore
      
      The backup default driver was writing blobs even if there
      was no data then calling a method to set it as not null. 
      This set the fields as not null and writing an empty string
      to the field.
      
      This patch changes the restore driver so that it checks the
      size of the blob data read from the backup image and only 
      writes to the field (and set not null) when there is data 
      to write.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for NULL blob fields.
     @ sql/backup/be_default.cc
        Changed to include check if there is data to write.
[11 Dec 2009 13:21] 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/93684

2904 Chuck Bell	2009-12-11
      BUG#49414 : Innodb tbl w/blob and text column with NULL no 
                  longer NULL after backup/restore
      
      The backup default driver was writing blobs even if there
      was no data and calling a method to set the field as not null. 
      This behavior writes an empty string.
      
      This patch changes the restore driver so that it checks the
      size of the blob data read from the backup image and only 
      writes to the field (and set not null) when there is data 
      to write.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for NULL blob fields.
     @ sql/backup/be_default.cc
        Changed to include check if there is data to write.
[11 Dec 2009 13: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/93686
[12 Dec 2009 17:15] 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/93781

2904 Chuck Bell	2009-12-12
      BUG#49414 : Innodb tbl w/blob and text column with NULL no 
                  longer NULL after backup/restore
      
      The backup default driver was writing blobs even if there
      was no data and calling a method to set the field as not null. 
      This behavior writes an empty string.
      
      This patch changes the restore driver so that it checks the
      size of the blob data read from the backup image and only 
      writes to the field (and set not null) when there is data 
      to write.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for NULL blob fields.
     @ sql/backup/be_default.cc
        Changed to include check if there is data to write.
[15 Dec 2009 20:51] 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/94278

2913 Chuck Bell	2009-12-15 [merge]
      BUG#49414 : Innodb tbl w/blob and text column with NULL no 
                  longer NULL after backup/restore
      
      The backup default driver was writing blobs even if there
      was no data and calling a method to set the field as not null. 
      This behavior writes an empty string.
      
      This patch changes the restore driver so that it checks the
      size of the blob data read from the backup image and only 
      writes to the field (and set not null) when there is data 
      to write.
     @ mysql-test/suite/backup/r/backup_blob.result
        Corrected result file.
     @ mysql-test/suite/backup/t/backup_blob.test
        Added test case for NULL blob fields.
     @ sql/backup/be_default.cc
        Changed to include check if there is data to write.
[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-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 1:28] Paul Dubois
Noted in 6.0.14 changelog.

For an InnoDB table with BLOB and TEXT columns containing NULL
values, those values became empty strings after BACKUP DATABASE and
RESTORE.