MySQL Bugs: #36778: Read operation during RESTORE leads to data loss

Bug #36778	Read operation during RESTORE leads to data loss
Submitted:	17 May 2008 20:09	Modified:	8 Aug 2008 22:00
Reporter:	Hema Sridharan
Status:	Closed
Category:	Server: Backup	Severity:	S2 (Serious)
Version:	mysql-6.0-backup-myisam	OS:	Linux
Assigned to:	Chuck Bell	Target Version:	6.0-beta
Triage:	D2 (Serious)

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)

Thank you for the bug report.

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;

See also BUG#36749.

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.

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.

Patch ready for review. Note: This patch requires the patch for BUG#36749.

http://lists.mysql.com/commits/48009

Patch approved.

Patch pushed to mysql-6.0-backup-myisam tree.

Patch is in main.

Fix was pushed into 6.0.6.

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.