Patch approved - verified that the test fails without the 36749 patch
and passes with it.
Chuck Bell wrote:
> #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
>
>
--
Jørgen Løland
