MySQL Bugs: #42359: Restore fails if uncommitted data from falcon table is included in backup image.

Bug #42359	Restore fails if uncommitted data from falcon table is included in backup image.
Submitted:	26 Jan 2009 22:31	Modified:	3 Jun 2009 23:29
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
Create database(try) and table using falcon storage engine.
Set autocommit to '0'
Insert some contents in table and do not commit.
Execute backup operation to try.bak
Restore from try.bak fails.

connection con1;

set autocommit=0;
create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);
select * from t3;

connection con2;

use try;
show tables;
select * from t3;
backup database try to 'try.a.bak';
mysql> restore from 'try.a.bak' overwrite;
ERROR 1683 (HY000): Could not restore database `try`

How to repeat:
connection con1;

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create database try;
Query OK, 1 row affected (0.01 sec)

mysql> use try;
Database changed
mysql> create table t3(id int) engine=falcon;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t3;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

connection con2;

mysql> use try;

mysql> show tables;
+---------------+
| Tables_in_try |
+---------------+
| t3            |
+---------------+
1 row in set (0.00 sec)

mysql> select * from t3;
Empty set (0.00 sec)

mysql> backup database try to 'try.bak';
+-----------+
| backup_id |
+-----------+
| 270       |
+-----------+
1 row in set (0.37 sec)

mysql> restore from 'try.bak' overwrite;
ERROR 1683 (HY000): Could not restore database `try`

I used mysql-6.0-backup tree.

Thank you for the report.

Verified as described. Other transactional engine InnoDB is not affected.

Some additional information related to this bug:

Show warnings immediately after failure of restore operation,

mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Error   | 1007 | Can't create database 'try'; database exists |
| Error   | 1683 | Could not restore database `try`             |
| Warning | 1761 | Operation aborted - data might be corrupted  |
+---------+------+----------------------------------------------+
3 rows in set (0.00 sec)

The server's error log shows the following information,

Version: '6.0.9-alpha-debug-log'  socket: '/home/hs221732/wl4230/mysql-6.0-backup/mysql-test/var/tmp/master.sock'  port: 9306  Source distribution
090127 19:08:01 [Note] Backup: Starting backup process
090127 19:08:01 [Note] Backup: Backing up 1 database(s) `try`
090127 19:08:01 [Note] Backup: Backup completed
090127 19:08:10 [Note] Restore: Starting restore process
090127 19:08:10 [Note] Restore: Restoring 1 database(s) `try`
090127 19:08:10 [ERROR] Restore: Could not restore database `try`
090127 19:08:10 [Warning] Restore: Operation aborted - data might be corrupted

The clue to the problem is here:
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level   | Code | Message                                      |
+---------+------+----------------------------------------------+
| Error   | 1007 | Can't create database 'try'; database exists |
| Error   | 1683 | Could not restore database `try`             |
| Warning | 1761 | Operation aborted - data might be corrupted  |
+---------+------+----------------------------------------------+

Could connection con1 still be active with a running transaction?

As suggested by Ann, the problem is that con1 has an active connection on table t3:
---------------
connection con1;

set autocommit=0;
create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);
select * from t3;
id
1
2
#commit <- fixes the problem

connection con2;

use try;
drop table t3;
ERROR 42S02: Unknown table 't3'
show warnings;
Level	Code	Message
Warning	178	Can't execute the given command because you have active locked tables or an active transaction
----------------

It is ok that RESTORE fails in this case, but the error messages are somewhat misleading. 

Suggested fix: 
* Make restore fail immediately when it fails to drop a table. 
* Change the error message stack so that it says that the table could not be dropped (instead of the current "Can't create database 'try'; database exists")

This bug depends on bug#43012 to be fixed, since the error messages of drop database are not in the warning list and are therefore not copied to the backup thread.

Depends on bug#989

The behavior of this script has changed with the patch for BUG#989. That patch changed DROP TABLE to block when an active transaction has accessed the table. 

Since "RESTORE ... OVERWRITE;" performs a DROP DATABASE (and therefore DROP TABLE), RESTORE is now blocked by active transactions that have accessed any of the tables in the database:

****************
connection con1;

set autocommit=0;
create database try;
use try;
create table t3(id int) engine=falcon;
insert into t3 values(1),(2);

connection con2;

use try;
backup database try to 'try.bup';
restore from 'try.bup'; # <-hangs until con2 commits/aborts

Typo in latest comment. It was meant to say

"restore from 'try.bup'; # <-hangs until con1 commits/aborts"

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

2796 Jorgen Loland	2009-04-15
      Bug#42359 - Restore fails if uncommitted data from falcon table is included in backup image.
      
      The bug was fixed by BUG#989. This patch contains a test case only
     @ mysql-test/suite/backup/r/backup_lock_conflict.result
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored.
     @ mysql-test/suite/backup/t/backup_lock_conflict.test
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored.

Approved

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

2796 Jorgen Loland	2009-04-28
      Bug#42359 - Restore fails if uncommitted data from falcon table is included in backup image.
      Bug#37303 - Transactions opened before restore see empty InnoDB tables after restore.
            
      The bugs were fixed by BUG#989. This patch contains a test case only
     @ mysql-test/suite/backup/r/backup_lock_conflict.result
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.
     @ mysql-test/suite/backup/t/backup_lock_conflict.test
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.

Ok to push by me. Please see email for comments.

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

2698 Jorgen Loland	2009-04-29
      Bug#42359 - Restore fails if uncommitted data from falcon table is included in backup image.
      Bug#37303 - Transactions opened before restore see empty InnoDB tables after restore.
                  
      The bugs were fixed by BUG#989. This commit contains a test case only.
     @ mysql-test/suite/backup/r/backup_lock_conflict.result
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.
     @ mysql-test/suite/backup/t/backup_lock_conflict.test
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.

Good to push.

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

2699 Jorgen Loland	2009-04-29
      Bug#42359 - Restore fails if uncommitted data from falcon table is included in backup image.
      Bug#37303 - Transactions opened before restore see empty InnoDB tables after restore.
                        
      The bugs were fixed by BUG#989. This commit contains a test case only.
     @ mysql-test/suite/backup/r/backup_lock_conflict.result
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.
     @ mysql-test/suite/backup/t/backup_lock_conflict.test
        Test that RESTORE is blocked by a concurrent transaction operating on a database being restored and that the ongoing transaction is not blocked by restore.

Merged to azalea June 2

Test case changes. No changelog entry needed.