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
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[1 Feb 2008 0:43] Peter Gulutzan
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'?
[1 Feb 2008 10:43] Sveta Smirnova
Thank you for the report.

Verified as described.
[20 May 2008 19:23] Øystein Grøvlen
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.
[20 May 2008 19:27] Øystein Grøvlen
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;
[23 May 2008 12:49] Øystein Grøvlen
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);
 }
[26 May 2008 14:18] Jørgen Løland
Seems to be duplicated in 36828. Will append the patch there to verify.
[28 May 2008 11:47] Bugs System
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.)
[28 May 2008 11:53] Øystein Grøvlen
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.
[28 May 2008 15:56] Chuck Bell
Patch reviewed. Comments sent to commits email.
[3 Jun 2008 11:29] Bugs System
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.)
[3 Jun 2008 17:02] Chuck Bell
Patch approved.
[4 Jun 2008 9:37] Jørgen Løland
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
...
[4 Jun 2008 12:13] Bugs System
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.)
[5 Jun 2008 9:28] Jørgen Løland
My 'approved' email did not show up on this issue, but the patch looks good.
[5 Jun 2008 12:26] Bugs System
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.)
[5 Jun 2008 12:29] Bugs System
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.)
[5 Jun 2008 12:41] Øystein Grøvlen
Patch has been push to mysql-6.0-backup branch
[7 Aug 2008 12:33] Øystein Grøvlen
Patch have been pushed to 6.0 main.
[8 Aug 2008 12:47] Øystein Grøvlen
This bug was fixed in 6.0.6.
[11 Aug 2008 20:37] Paul Dubois
Noted in 6.0.6 changelog.

For InnoDB tables, loss of data resulted from performing inserts
concurrently with a RESTORE operation.
[14 Sep 2008 1:01] Bugs System
Pushed into 6.0.7-alpha  (revid:ogrovlen@sun.com-20080605122631-b803ysiwv8762pq0) (version source revid:vvaintroub@mysql.com-20080804094710-jb2qpqxpf2ir2gf3) (pib:3)