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

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.