MySQL Bugs: #50697: Backup: storage engine with both native and consistent snapshot fails

Bug #50697	Backup: storage engine with both native and consistent snapshot fails
Submitted:	28 Jan 2010 18:57	Modified:	2 Mar 2010 1:25
Reporter:	Chuck Bell	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.14	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
When a storage engine supports both consistent snapshot and has a native driver, the code incorrectly attempts to use the native driver. This results in a failure in the storage engine because the table being requested does not exist in the storage engine -- because it is managed by the partition storage engine.

The backup kernel should recognize when this condition occurs and not use the native backup driver but rather use the consistent snapshot driver.

This problem was originally reported as a bug against PBXT - https://bugs.launchpad.net/pbxt/+bug/508960.

Further diagnosis has shown the problem is in the backup code.

How to repeat:
Test Case
---------
USE test;
CREATE TABLE t_part_myisam ( i INT PRIMARY KEY ) ENGINE=MYISAM 
  PARTITION BY HASH(i) PARTITIONS 2;
CREATE TABLE t_part_pbxt ( i INT PRIMARY KEY ) ENGINE=PBXT 
  PARTITION BY HASH(i) PARTITIONS 2;
CREATE TABLE t_part_innodb ( i INT PRIMARY KEY ) ENGINE=INNODB 
  PARTITION BY HASH(i) PARTITIONS 2;
backup database test to 't1.bak';
SELECT * from mysql.backup_history \G

Note: You will need to use the PBXT code referenced in the bug report on launchpad. 

This is what the code does now:

mysql> use test;
Database changed
mysql> CREATE TABLE t_part_pbxt ( i INT PRIMARY KEY ) ENGINE=PBXT PARTITION BY HASH(i) PARTITIONS 2;
Query OK, 0 rows affected (0.59 sec)

mysql> CREATE TABLE t_part_innodb ( i INT PRIMARY KEY ) ENGINE=INNODB PARTITION BY HASH(i) PARTITIONS 2;
Query OK, 0 rows affected (0.06 sec)

mysql> backup database test to 't1.bak';
ERROR 1686 (HY000): Error when polling PBXT backup driver for its data

mysql> SELECT * from mysql.backup_history \G
*************************** 1. row ***************************
...
            command: backup database test to 't1.bak'
            drivers: Snapshot, PBXT
1 row in set (0.00 sec)

The error is generated because the pbxt native driver doesn't know what to do with a partition table. Notice the driver reported was both the consistent snapshot and the native driver for PBXT -- this is wrong.

Here's what the code *should* do:

mysql> use test;
Database changed
mysql> CREATE TABLE t_part_pbxt ( i INT PRIMARY KEY ) ENGINE=PBXT PARTITION BY HASH(i) PARTITIONS 2;
Query OK, 0 rows affected (0.91 sec)

mysql> CREATE TABLE t_part_innodb ( i INT PRIMARY KEY ) ENGINE=INNODB PARTITION BY HASH(i) PARTITIONS 2;
Query OK, 0 rows affected (0.05 sec)

mysql> backup database test to 't1.bak';
+-----------+
| backup_id |
+-----------+
| 276       |
+-----------+
1 row in set (0.05 sec)

mysql> SELECT * from mysql.backup_history \G
*************************** 1. row ***************************
...
            command: backup database test to 't1.bak'
            drivers: Snapshot
1 row in set (0.00 sec)

mysql> 

Notice the driver used is Snapshot - this is because both PBXT and InnoDB support consistent snapshot.

Suggested fix:
Add a check to the driver recognition code in backup_info.cc to recognize when a table has partitions and if the table has partitions and a native driver is present to switch to the default driver selection which will correctly identify whether the engine supports consistent snapshot.

There are a couple of things we need to do to fix this.

a) the partitions_have_same_engine() method should *not* be checking for
   consistent snapshot capability
b) if a storage engine has partitions and a native driver is present,
   the code should fall to the default driver selection
c) if a storage engine has partitions and also supports consistent
   snapshot, the code should fall to the default driver selection
d) changing the storage engine reference from partition to a specific
   storage engine should be governed by the above conditions

I see that (a) must be rejected. The check inside the partition loop is necessary to preserve the correct storage engine reference.

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/98667

2942 Chuck Bell	2010-01-29
      BUG#50697 : Backup: storage engine with both native and consistent snapshot fails
      
      When a storage engine supports both consistent snapshot and has a native driver,
      the code incorrectly attempts to use the native driver. This results in a 
      failure in the storage engine because the table being requested does not exist 
      in the storage engine -- because it is managed by the partition storage engine.
      
      This patch corrects the behavior by adding a check for whether the table has
      partitions and using this to bypass the native backup check. This allows backup
      to use a storage engine that supports consistent snapshot when backing up
      partitioned tables. Otherwise, the default driver is used with the partition
      storage engine. 
     @ mysql-test/suite/backup_engines/r/backup_partition.result
        Corrected result file.
     @ mysql-test/suite/backup_engines/t/backup_partition.test
        New test case.
     @ sql/backup/backup_info.cc
        Changed code to recognize when a storage engine has both a
        native driver and supports consistent snapshot.

Approved pending changes. Pleas see email for details.

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/99149

3081 Chuck Bell	2010-02-03
      BUG#50697 : Backup: storage engine with both native and consistent snapshot fails
      
      When a storage engine supports both consistent snapshot and has a native
      driver, the code incorrectly attempts to use the native driver for a table
      that has partitions defined. This results in a failure in the storage engine
      because the table being requested does not exist in the storage engine -- 
      because it is managed by the partition storage engine.
      
      This patch corrects the behavior by adding a check for whether the table 
      has partitions and using this to bypass the native backup check. This allows
      backup to use a storage engine that supports consistent snapshot when 
      backing up partitioned tables. Otherwise, the default driver is used with 
      the partition storage engine. 
     @ mysql-test/suite/backup_engines/r/backup_partition.result
        Corrected result file.
     @ mysql-test/suite/backup_engines/t/backup_partition.test
        New test case.
     @ sql/backup/backup_info.cc
        Changed code to recognize when a storage engine has both a
        native driver and supports consistent snapshot.

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/99157

3826 Chuck Bell	2010-02-03
      BUG#50697 : Backup: storage engine with both native and consistent snapshot fails
      
      When a storage engine supports both consistent snapshot and has a native
      driver, the code incorrectly attempts to use the native driver for a table
      that has partitions defined. This results in a failure in the storage engine
      because the table being requested does not exist in the storage engine -- 
      because it is managed by the partition storage engine.
      
      This patch corrects the behavior by adding a check for whether the table 
      has partitions and using this to bypass the native backup check. This allows
      backup to use a storage engine that supports consistent snapshot when 
      backing up partitioned tables. Otherwise, the default driver is used with 
      the partition storage engine. 
     @ mysql-test/suite/backup_engines/r/backup_partition.result
        Corrected result file.
     @ mysql-test/suite/backup_engines/t/backup_partition.test
        New test case.
     @ sql/backup/backup_info.cc
        Changed code to recognize when a storage engine has both a
        native driver and supports consistent snapshot.

Patch pushed to mysql-next-mr-backup, mysql-backup-backport, and merged into mysql-6.0-backup.

Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:anurag.shekhar@sun.com-20100204092530-ci0u47ufmi3vzfs0) (merge vers: 6.0.14-alpha) (pib:16)

Noted in 6.0.14 changelog.

When a storage engine supports both consistent snapshot and has a
native driver, the BACKUP DATABASE code incorrectly attempted to use
the native driver for a table that has partitions defined.