MySQL Bugs: #34210: Backup: lost InnoDB rows if concurrent restore

Bug #34210	Backup: lost InnoDB rows if concurrent restore
Submitted:	1 Feb 2008 0:43	Modified:	11 Aug 2008 20:37
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 64-bit)
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any

Description:
I create an InnoDB table.
I insert four rows.
I backup the database.
On another connection, I insert two rows.
I restore the database.
On another connection, I insert one row.
I rollback;
I select, and see only one row.
Six rows have disappeared.

How to repeat:
Start two mysql clients. Call them T1 and T2.

On T1, say:
use test
set @@autocommit=0;
create table t (s1 char(1) primary key) engine=innodb;
insert into t values ('a');
commit;
insert into t values ('b'),('c'),('d');
backup database test to '83';

On T2, say:
use test
set @@autocommit=0;
insert into t values ('e');
insert into t values ('f');

On T1, say:
restore from '83';

On T2, say:
insert into t values ('g');
commit;
select * from t;

On T1, say:
rollback;
select * from t;

The result of the final SELECT on T2 looks like this:
mysql> select * from t;
+----+
| s1 |
+----+
| g  |
+----+
1 row in set (0.00 sec)

The result of the final SELECT on T1 looks like this:

mysql> select * from t;
+----+
| s1 |
+----+
| g  |
+----+
1 row in set (0.00 sec)

So what happened to 'a','b','c','d','e','f'?

Thank you for the report.

Verified as described.

I have isolated the problem with the following test script.  Note that only a single client is needed to reproduce the problem:

--source include/have_innodb.inc
use test;
set @@autocommit=0;
create table t (s1 char(1)) engine=innodb;
insert into t values ('a');
commit;
backup database test to '83';
restore from '83';
select * from t;
rollback;
select * from t;

Output:
use test;
set @@autocommit=0;
create table t (s1 char(1)) engine=innodb;
insert into t values ('a');
commit;
backup database test to '83';
backup_id
1
restore from '83';
backup_id
2
select * from t;
s1
a
rollback;
select * from t;
s1

So rollback is undoing a transaction that was committed before backup.

Committing after restore fixes the problem.  Test case:

--source include/have_innodb.inc
use test;
set @@autocommit=0;
create table t (s1 char(1)) engine=innodb;
insert into t values ('a');
commit;
backup database test to '83';
restore from '83';
commit;
select * from t;
rollback;
select * from t;

Committed the following patch which makes my repro run without failures.
Forgot to add a test case, so I guess I will get this in return ...

--- a/sql/backup/be_default.cc	2008-05-14 18:28:30 +02:00
+++ b/sql/backup/be_default.cc	2008-05-23 14:39:51 +02:00
@@ -622,6 +622,8 @@ result_t Restore::truncate_table(TABLE *
 result_t Restore::end()
 {
   DBUG_ENTER("Restore::end");
+  ha_autocommit_or_rollback(m_thd, 0);
+  end_active_trans(m_thd);
   close_thread_tables(m_thd);
   DBUG_RETURN(OK);
 }

Seems to be duplicated in 36828. Will append the patch there to verify.

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

ChangeSet@1.2622, 2008-05-28 13:46:39+02:00, og136792@siv07.norway.sun.com +3 -0
  Bug#34210: Perform commit at end of restore.
  (Restore should be a transaction by itself.)

Committed a new version with test case.
Reviewers:  I suspect I should have added some error handling for the commit, but
it is not consistent how this is done in other parts of the code.  Seeking advice here.

Patch reviewed. Comments sent to commits email.

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

2625 Oystein Grovlen	2008-06-03
      Bug#34210: Perform commit at end of restore.
      (Restore should be a transaction by itself.)

Patch approved.

The patch doesn't compile on my system. I'm using Ubuntu/AMD64/gcc v4.2.3. The output from make:

...
data_backup.cc: In function ‘int backup::restore_table_data(THD*, Restore_info&, backup::Input_stream&)’:
data_backup.cc:1612: error: jump to label ‘error’
data_backup.cc:1484: error:   from here
data_backup.cc:1603: error:   crosses initialization of ‘THD* thd’
data_backup.cc:1612: error: jump to label ‘error’
data_backup.cc:1440: error:   from here
data_backup.cc:1603: error:   crosses initialization of ‘THD* thd’
data_backup.cc:1612: error: jump to label ‘error’
data_backup.cc:1404: error:   from here
data_backup.cc:1603: error:   crosses initialization of ‘THD* thd’
make[3]: *** [data_backup.lo] Error 1
...

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

2627 Oystein Grovlen	2008-06-04
      Bug#34210: Perform commit at end of restore.
      (Restore should be a transaction by itself.)

My 'approved' email did not show up on this issue, but the patch looks good.

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

2630 Oystein Grovlen	2008-06-05
      Bug#34210: Perform commit at end of restore.
      (Restore should be a transaction by itself.)

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

2630 Oystein Grovlen	2008-06-05
      Bug#34210: Perform commit at end of restore.
      (Restore should be a transaction by itself.)

Patch has been push to mysql-6.0-backup branch

Patch have been pushed to 6.0 main.

This bug was fixed in 6.0.6.

Noted in 6.0.6 changelog.

For InnoDB tables, loss of data resulted from performing inserts
concurrently with a RESTORE operation.

Pushed into 6.0.7-alpha  (revid:ogrovlen@sun.com-20080605122631-b803ysiwv8762pq0) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)