MySQL Bugs: #37303: Transactions opened before restore see empty InnoDB tables after restore.

Bug #37303	Transactions opened before restore see empty InnoDB tables after restore.
Submitted:	10 Jun 2008 9:34	Modified:	3 Jun 2009 23:27
Reporter:	Øystein Grøvlen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	6.0-backup	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
A transaction that is running during restore, will after restore has completed see the restored InnoDB tables as empty, regardless of any operations performed before restore.

(This is kind of related to Bug#12347 InnoDB: DROP TABLE ignores locks.  The same behavior is observed if one drops and recreates the table.)

How to repeat:
The following test script reproduces the problem:

--source include/have_innodb.inc

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
create database test1;
create database test2;
create table test1.t (s1 char(1) primary key) engine=innodb;
create table test2.t (s1 char(1) primary key) engine=innodb;
insert into test1.t values ('a');
insert into test2.t values ('a');

connection con2;
backup database test1 to '83';

connection con1;
set @@autocommit=0;
insert into test1.t values ('b');
insert into test2.t values ('b');
select * from test1.t;
select * from test2.t;

connection con2;
restore from '83';

connection con1;
select * from test1.t;
select * from test2.t;
commit;
select * from test1.t;
select * from test2.t;
commit;

---------------------------------------------------------------------
Output as follows:

create database test1;
create database test2;
create table test1.t (s1 char(1) primary key) engine=innodb;
create table test2.t (s1 char(1) primary key) engine=innodb;
insert into test1.t values ('a');
insert into test2.t values ('a');
backup database test1 to '83';
backup_id
1
set @@autocommit=0;
insert into test1.t values ('b');
insert into test2.t values ('b');
select * from test1.t;
s1
a
b
select * from test2.t;
s1
a
b
restore from '83';
backup_id
2
select * from test1.t;
s1
select * from test2.t;
s1
a
b
commit;
select * from test1.t;
s1
a
select * from test2.t;
s1
a
b
commit;

--------------------------------------------------------------------------

Note that the output from the select of test1.t show no rows after restore.  After commit, the new transaction sees the backed up version of the table.

Suggested fix:
I think this should be fixed by preventing concurrent transactions during restore.  This can be done in two ways:

1. Abort ongoing transactions at start of restore.
2. Block restore while there is ongoing transactions.

Many thanks for writing a bug report.

You are right, this is a very weird behaviour.

Verified as described by using MySQL 6.0 bzr tree from last night and test from above.

I also tested with FALCON and its not possible here to restore. So this is InnoDB related.

Since the restore probably imports the tables in one big sweep, it is natural that a consistent read sees the table as it was BEFORE the import.

Setting this as 'Won't fix' because think SOME queries do want to see the table as it was before the import.

This was intended to befiled as a InnoDB bug.  It is more a question whether backup should do anything to try to overcome deficiencies in the MySQL/InnoDB integration which I guess is a result of BUG#12347.  If that was fixed, restore would not be able to drop the old version of the table as long as there were ongoing transactions, and this anomaly would never occur.

Heikki: The problem is that queries do NOT to see the table as it was before the restore.  They see an empty table.

Just dicovered that my previous comment came out wrong.  This is NOT intended to be filed as a InnoDB bug. I will experiment to see if locking the table before backup makes any difference.

While this is only an issue for InnoDB and not for Falcon for transactions that have changed the database before restore, both storage engines shows this behavior for transactions that read the database prior to restore.  E.g., see the following script/result:

SCRIPT:
----------------------------------------------------------
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
create database test1;
create table test1.t (s1 char(1) primary key) engine=falcon;
insert into test1.t values ('a');
insert into test1.t values ('b');
insert into test1.t values ('c');

connection con2;
backup database test1 to '83';

insert into test1.t values ('d');
insert into test1.t values ('e');
insert into test1.t values ('f');

connection con1;
set @@autocommit=0;
select * from test1.t;

connection con2;
restore from '83';

connection con1;
select * from test1.t;
commit;
select * from test1.t;
commit;
----------------------------------------------

RESULT:
----------------------------------------------
create database test1;
create table test1.t (s1 char(1) primary key) engine=falcon;
insert into test1.t values ('a');
insert into test1.t values ('b');
insert into test1.t values ('c');
backup database test1 to '83';
backup_id
270
insert into test1.t values ('d');
insert into test1.t values ('e');
insert into test1.t values ('f');
set @@autocommit=0;
select * from test1.t;
s1
a
b
c
d
e
f
restore from '83';
backup_id
271
select * from test1.t;
s1
commit;
select * from test1.t;
s1
a
b
c
commit;

I checked how Falcon behaves with truncate table, and it seems to be similar to restore.  I can truncate a table that open transactions has accessed.  After truncate has been performed, these transaction will see an empty table until commit. The conclusion is that truncate table breaks the multi-versioning.  If that is OK for TRUNCATE TABLE, I think it should be OK for RESTORE to do the same.

Since behavior is similar for other commands, e.g., see comment in this report about TRUNCATE TABLE, I ask triage team to reconsider whether this needs to be fixed for 6.0.

I did experiment on the current main-6.0 tree and it seems that BUG#989 (aka. transactional metadata locks) is still not fixed. That is, using a table in one transaction does not prevent performing DML operations on the same table from another connection.

Still waiting for BUG#989 fix.

Rafal,

Bug#989 (If DROP TABLE while there's an active transaction, wrong binlog order) is about DDLs during active transactions.

Is your comment about DML a typo?

Yes, it was a typo. I meant DDL.

Rafal, Bug#989 was pushed to main on Mar 31.

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.

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.

Test provided by patch BUG#42359.

Merged to azalea June 2

Test case changes. No changelog entry needed.