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:	Oystein Grovlen	Email Updates:
Status:	Closed
Category:	Server: Backup	Severity:	S2 (Serious)
Version:	6.0-backup	OS:	Any
Assigned to:	Jorgen Loland	Target Version:	6.0-beta
Triage:	Triaged: D2 (Serious)

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.