Bug #36778 Read operation during RESTORE leads to data loss
Submitted: 17 May 2008 18:09 Modified: 8 Aug 2008 20:00
Reporter: Hema Sridharan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S2 (Serious)
Version:mysql-6.0-backup-myisam OS:Linux
Assigned to: Chuck Bell CPU Architecture:Any

[17 May 2008 18:09] Hema Sridharan
Description:
1) I create Database db1 and db2.
2) I create tables (account,t1) in db1 and t1 in db2.
3) Load lots of data in db2.t1 and perform Backup Database operation.
4) Drop database db2 alone and perform Restore. During Restore, from another connection check the contents of database that is getting restored (db2) by doing select operation.
5) After Restore completion, check the contents of db2.

Tables are shown empty.

==> If there is simultaneous read attempt on the database that is being restored, data loss noticed after RESTORE

How to repeat:
CREATE DATABASE db1;
CREATE DATABASE db2;
USE db1;
CREATE TABLE account(aid int, bid int, balance decimal, filler char(100));
CREATE TABLE t1(letter char(10));

USE db2;
CREATE TABLE t1(id int, no int, balance decimal, filler char(100));

Loads Lots of data in db2.t1 and perform Backup Database Operation.

BACKUP DATABASE db2 to '/tmp/b2';

DROP DATABASE db2;
RESTORE FROM '/tmp/b2';

During Restore, from another connection try to read the contents of the database that is getting restored(for eg: show tables from db2, select count(*) from db2.t1). After Restore completes, there are no contents seen in db2.t1. It appears as Zero.

mysql> show tables;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |           |
+---------------+
2 rows in set (0.02 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
[29 May 2008 23:24] MySQL Verification Team
Thank you for the bug report.
[9 Jun 2008 12:01] Rafal Somla
Here is a test script with which I was able to reproduce the issue. The timing of sleep is very sensitive - if too short, test will fail because db1.t1 was not created yet; if too long the issue will not show up. I was able to recreate the problem only when native MyISAM driver was used.

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

--connection con1

create database db1;
use db1;
create table t1 (a int) engine=myisam;

insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9),(0);

insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;

insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;
insert into t1 select * from t1;

--connection con2

use test;

create table t2 (a int);

--connection con1

backup database db1 to 'db1.bak';

drop database db1;

--send restore from 'db1.bak'

--connection con2

-- sleep 0.004
select count(*) from db1.t1;

--connection con1
--reap

select count(*) from t1;
[11 Jun 2008 7:35] Lars Thalmann
See also BUG#36749.
[17 Jun 2008 14:35] Chuck Bell
This bug is fixed by the patch for BUG#36749. A patch to the test in the BUG#36749 patch will be presented in this bug report.
[17 Jun 2008 14:42] 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/48009

2619 Chuck Bell	2008-06-17
      BUG#36778 Read operation during RESTORE leads to data loss
      
      This bug is related to BUG#36749. The patch for BUG#36749 fixes this problem. The problem is
      while the native driver is running, any open of the table alters its status thereby leaving
      the table in an incorrect state. In this case, the table is emptied. Locking the tables
      before restore (as done in BUG#36749) fixes the problem and forces other connections to wait
      until the restore is complete to run.
[17 Jun 2008 14:42] Chuck Bell
Patch ready for review. Note: This patch requires the patch for BUG#36749.

http://lists.mysql.com/commits/48009
[19 Jun 2008 7:49] Jørgen Løland
Patch approved.
[25 Jun 2008 14:16] Chuck Bell
Patch pushed to mysql-6.0-backup-myisam tree.
[8 Aug 2008 13:42] Chuck Bell
Patch is in main.
[8 Aug 2008 16:32] Chuck Bell
Fix was pushed into 6.0.6.
[8 Aug 2008 20:00] Paul DuBois
Noted in 6.0.6 changelog.

Data loss could be caused by attempts to read data from a database
being restored during a RESTORE operation.