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: | |
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 |
[3 Dec 2009 19:34]
Victor Kirkebo
[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.