| Bug #36782 | View and its related table data lost after Restore. | ||
|---|---|---|---|
| Submitted: | 18 May 2008 4:17 | Modified: | 8 Aug 2008 19:57 |
| Reporter: | Hema Sridharan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | mysql-6.0-backup-myisam | OS: | Linux |
| Assigned to: | Chuck Bell | CPU Architecture: | Any |
[3 Jun 2008 21:23]
Hema Sridharan
The data loss occurs only when views are created from tables that uses Myisam storage engines(Native drivers)
[9 Jun 2008 9:06]
Rafal Somla
The problem happens only when both table and view are backed up and when native MyISAM backup is used. A possible cause could be a bad interaction between the si_objects.cc code for detecting base tables of a view (which calls open_tables()) and the MyISAM native driver code for opening and locking tables.
[17 Jun 2008 15:10]
Chuck Bell
This bug is also fixed by BUG#36749.
[17 Jun 2008 15:21]
Bugs System
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/48013 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.
[17 Jun 2008 15:23]
Chuck Bell
Patch ready for review. Note: This patch requires patches for BUG#36749 and BUG#36778. http://lists.mysql.com/commits/48013
[19 Jun 2008 7:45]
Jørgen Løland
Patch approved
[19 Jun 2008 20:11]
Bugs System
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/48200 2618 Chuck Bell 2008-06-19 BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being Restored. Backup kernel should lock all tables during restore but it was not doing so for native drivers. This caused the MyISAM storage engine to return incorrect and loss of data when tables were being restored. Note: This patch also fixes BUG#36778 and BUG#36782.
[20 Jun 2008 14:01]
Bugs System
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/48242 2618 Chuck Bell 2008-06-20 BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being Restored. Backup kernel should lock all tables during restore but it was not doing so for native drivers. This caused the MyISAM storage engine to return incorrect and loss of data when tables were being restored. Note: This patch also fixes BUG#36778 and BUG#36782.
[25 Jun 2008 14:00]
Bugs System
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/48493 2618 Chuck Bell 2008-06-25 BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being restored. Backup kernel should lock all tables during restore but it was not doing so for native drivers. This caused the MyISAM storage engine to return incorrect and loss of data when tables were being restored. Note: This patch also fixes BUG#36778 and BUG#36782.
[25 Jun 2008 14:00]
Bugs System
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/48494 2618 Chuck Bell 2008-06-25 BUG#36749 Data Loss after Restore, if Trigger fired on the table that is being restored. Backup kernel should lock all tables during restore but it was not doing so for native drivers. This caused the MyISAM storage engine to return incorrect and loss of data when tables were being restored. Note: This patch also fixes BUG#36778 and BUG#36782.
[25 Jun 2008 14:15]
Chuck Bell
Patch pushed to mysql-6.0-backup-myisam tree.
[8 Aug 2008 13:31]
Chuck Bell
Patch is in main.
[8 Aug 2008 16:32]
Chuck Bell
Fix was pushed into 6.0.6.
[8 Aug 2008 19:57]
Paul DuBois
Noted in 6.0.6 changelog. For a view that referred to a MyISAM table, the contents of the table could be empty after BACKUP DATABASE followed by RESTORE.
Description: 1) Create database (v)and table t1 . 2) Create view v1 from t1. 3) Insert some values in to table t1. 4) Perform Backup Database Operation. 5) Drop Database and then perform Restore. 6) Contents of the table t1 and view v1 are empty. How to repeat: mysql> create database v; Query OK, 1 row affected (0.00 sec) mysql> use v; Database changed mysql> create table t1(name char(10)); Query OK, 0 rows affected (0.03 sec) mysql> insert into t1 values('a'),('b'),('c'),('d'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t1; +------+ | name | +------+ | a | | b | | c | | d | +------+ 4 rows in set (0.00 sec) mysql> create view v1 as select * from t1; Query OK, 0 rows affected (0.00 sec) mysql> select * from v1; +------+ | name | +------+ | a | | b | | c | | d | +------+ 4 rows in set (0.00 sec) mysql> backup database v to '/tmp/v'; +-----------+ | backup_id | +-----------+ | 27 | +-----------+ 1 row in set (0.06 sec) mysql> drop database v; Query OK, 2 rows affected (0.00 sec) mysql> restore from '/tmp/v'; +-----------+ | backup_id | +-----------+ | 28 | +-----------+ 1 row in set (0.06 sec) mysql> select * from v1; Empty set (0.01 sec) mysql> select * from t1; Empty set (0.00 sec) The data contents of view and table t1 are lost after Restore which is a disaster.