MySQL Bugs: #36749: Data Loss after Restore, if Trigger fired on the table that is being Restored.

Bug #36749	Data Loss after Restore, if Trigger fired on the table that is being Restored.
Submitted:	16 May 2008 4:45	Modified:	8 Aug 2008 20:05
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 two databases(db1 and db2),tables (account in db1) and(t1 and t2 in db2).
2)I create object Trigger in db1 that inserts values in db2.t2 after every insert values into db1.account.
3)I dump lots of data in db2.t1 so that my Backup Database and Restore of db2 will take some time to complete.I can utilize this time to fire the trigger from db1.account to db2.t2.(I take backup of db2 alone)
4) While db2 is getting Restored, I start firing the trigger from db1.account and after the Restore completion I notice that the db2.t2 has only values that were fired during Restore, whereas loses all its previous contents, which is a Disaster as it eventually results in DATA LOSS.

Note: There is data loss after Restore if trigger is fired on the table that is being restored.

How to repeat:
CREATE DATABASE db1;
CREATE DATABASE db2;
USE db1;
CREATE TABLE account(aid int, bid int, balance decimal, filler char(100));
CREATE TRIGGER trg AFTER INSERT INTO account FOR EACH ROW 
 BEGIN
    INSERT INTO db2.t2 VALUES('*');
 END;

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

In created a script to load lots of data in tables account and db2.t2.

While it takes time to Load data, check the data contents of db1.account and db2.t2

mysql> select count(*) from account;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from db2.t2;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

After complete Loading of Data, Perform the BACKUP DATABASE operation, Drop Database db2 alone and Perform restore.This Restore while take few seconds to complete, from other connection fire trigger from db1.account.(Try to insert some values in to db1.account)

mysql> insert into account values(2000000,90000078,276.299,'i am finishing');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select count(*) from db1.account;
+----------+
| count(*) |
+----------+
|     1001 |
+----------+
1 row in set (0.00 sec)

After Restore is complete, Check for contents in db2.

mysql> use db2;

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 20000000 |
+----------+
1 row in set (0.00 sec)

mysql> select * from t2;
+--------+
| letter |
+--------+
| *      |
+--------+
1 row in set (0.00 sec)

Here if you notice db2.t2 has only one value and previous contents which were backed up are Lost.
This issue is visible only when we try to insert some new values during Restore of the Database.

Thank you for the report.

Verified as described.

triage: need feedback from Lars abut the type of bug this is and expected operations allowed during restore. - email sent and setting to 255

Here is a test script with which I was able to reproduce the isssue. See a similar test in BUG#36778.

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

delimiter |;

create trigger trg after insert on t2 for each row
begin
  insert into db1.t1 values ('99');
end|

delimiter ;|

--connection con1

backup database db1 to 'db1.bak';

drop database db1;

--send restore from 'db1.bak'

--connection con2

-- sleep 0.004
insert into test.t2 values (0);

--connection con1

--reap

select count(*) from t1;

Patch ready for review.

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

2618 Chuck Bell	2008-06-17
      BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being Restored.
      
      The MyISAM native driver is not locking its tables. This results in data losson restore
      as either a collision or an overwrite condition occurs.
      
      This patch corrects the problem by taking an exclusive name lock on all tables prior to
      restore of the data (after the metadata step).

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

2618 Chuck Bell	2008-06-17
      BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being Restored.
      
      The MyISAM native driver is not locking its tables. This results in data losson restore
      as either a collision or an overwrite condition occurs.
      
      This patch corrects the problem by taking an exclusive name lock on all tables prior to
      restore of the data (after the metadata step).

Patch approved. Verified that attached tests fail without the patch and pass with it.

New patch developed to address the following:

* useless method declaration (get_all_snapshot_tables)
* comments on commit
* removed TABLE_LIST from si_objects methods
* use exclusive locks only for name locks
* corrected internationalization strings

Also combined patches for BUG#36778 and BUG#36782 since these contained modifications to the test included in this patch.

Patch approved

Conditionally approved with requirement that Image_info::Tables class becomes protected, not public.

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

Fix was pushed into 6.0.6.

Noted in 6.0.6 changelog.

Data loss could be caused by activation of a trigger for a MyISAM
table being restored by a RESTORE operation.

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080625135945-n05noodqx2k2gs2x) (version source revid:v.narayanan@sun.com-20080820070709-nx09bk6qx81osd5s) (pib:3)