From: Date: June 17 2008 5:21pm
Subject: bzr commit into mysql-6.0-backup branch (cbell:2620) Bug#36749, Bug#36782
List-Archive: http://lists.mysql.com/commits/48013
X-Bug: 36782
Message-Id: <200806171521.m5HFL1RF022876@mail.mysql.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

#At file:///C:/source/bzr/mysql-6.0-bug-36749/

 2620 Chuck Bell	2008-06-17
      BUG#36782 View and its related table data lost after Restore. 
      
      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 opened when the dependencies of the view are checked. Locking the tables
      before restore (as done in BUG#36749) fixes the problem.
modified:
  mysql-test/r/backup_lock_myisam.result
  mysql-test/t/backup_lock_myisam.test

per-file messages:
  mysql-test/r/backup_lock_myisam.result
    New result file.
  mysql-test/t/backup_lock_myisam.test
    New test added to backup_lock_myisam test to ensure the problem is detected.
=== modified file 'mysql-test/r/backup_lock_myisam.result'
--- a/mysql-test/r/backup_lock_myisam.result	2008-06-17 14:42:06 +0000
+++ b/mysql-test/r/backup_lock_myisam.result	2008-06-17 15:21:09 +0000
@@ -2,6 +2,7 @@ SET DEBUG_SYNC= 'RESET';
 From con1:
 DROP DATABASE IF EXISTS db1;
 DROP DATABASE IF EXISTS db2;
+DROP DATABASE IF EXISTS db3;
 Create database 1 and a table then populate it
 CREATE DATABASE db1;
 CREATE TABLE db1.t1 (a INT) ENGINE=MYISAM;
@@ -135,7 +136,53 @@ a
 11
 12
 13
+SET DEBUG_SYNC= 'RESET';
+Create a database with a table and a view using the MyISAM engine.
+CREATE DATABASE db3;
+CREATE TABLE db3.t1(name CHAR(10)) ENGINE=MYISAM;
+INSERT INTO db3.t1 VALUES('A'),('B'),('C'),('D');
+CREATE VIEW db3.v1 AS SELECT * FROM db3.t1;
+Show the data before backup
+SELECT * FROM db3.t1;
+name
+A
+B
+C
+D
+SELECT * FROM db3.v1;
+name
+A
+B
+C
+D
+Backup the database.
+BACKUP DATABASE db3 TO 'db3.bak';
+backup_id
+#
+Now drop then restore the database.
+DROP DATABASE db3;
+RESTORE FROM 'db3.bak';
+backup_id
+#
+Show the table and view.
+SHOW FULL TABLES FROM db3;
+Tables_in_db3	Table_type
+t1	BASE TABLE
+v1	VIEW
+Show the data after restore.
+SELECT * FROM db3.v1;
+name
+A
+B
+C
+D
+SELECT * FROM db3.t1;
+name
+A
+B
+C
+D
 cleanup
 DROP DATABASE db1;
 DROP DATABASE db2;
-SET DEBUG_SYNC= 'RESET';
+DROP DATABASE db3;

=== modified file 'mysql-test/t/backup_lock_myisam.test'
--- a/mysql-test/t/backup_lock_myisam.test	2008-06-17 14:42:06 +0000
+++ b/mysql-test/t/backup_lock_myisam.test	2008-06-17 15:21:09 +0000
@@ -22,11 +22,15 @@ connect(breakpoints, localhost, root,,);
 --disable_warnings
 DROP DATABASE IF EXISTS db1;
 DROP DATABASE IF EXISTS db2;
+DROP DATABASE IF EXISTS db3;
 --enable_warnings
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/db1.bak
 
+--error 0, 1
+--remove_file $MYSQLTEST_VARDIR/master-data/db3.bak
+
 --echo Create database 1 and a table then populate it
 CREATE DATABASE db1;
 CREATE TABLE db1.t1 (a INT) ENGINE=MYISAM;
@@ -181,11 +185,45 @@ SELECT * FROM db1.t2;
 
 SELECT * FROM db1.t3;
 
+SET DEBUG_SYNC= 'RESET';
+
+#
+# BUG#36782 - Data loss with restore of view.
+#
+
+--echo Create a database with a table and a view using the MyISAM engine.
+CREATE DATABASE db3;
+CREATE TABLE db3.t1(name CHAR(10)) ENGINE=MYISAM;
+INSERT INTO db3.t1 VALUES('A'),('B'),('C'),('D');
+CREATE VIEW db3.v1 AS SELECT * FROM db3.t1;
+
+--echo Show the data before backup
+SELECT * FROM db3.t1;
+SELECT * FROM db3.v1;
+
+--echo Backup the database.
+--replace_column 1 #
+BACKUP DATABASE db3 TO 'db3.bak';
+
+--echo Now drop then restore the database.
+DROP DATABASE db3;
+--replace_column 1 #
+RESTORE FROM 'db3.bak';
+
+--echo Show the table and view.
+SHOW FULL TABLES FROM db3;
+
+--echo Show the data after restore.
+SELECT * FROM db3.v1;
+SELECT * FROM db3.t1;
+
 --echo cleanup
 DROP DATABASE db1;
 DROP DATABASE db2;
-
-SET DEBUG_SYNC= 'RESET';
+DROP DATABASE db3;
 
 --error 0, 1
 --remove_file $MYSQLTEST_VARDIR/master-data/db1.bak
+
+--error 0, 1
+--remove_file $MYSQLTEST_VARDIR/master-data/db3.bak