| Bug #34767 | Existing DB contents are lost, if I attempt to restore a corrupted backup file. | ||
|---|---|---|---|
| Submitted: | 22 Feb 2008 20:58 | Modified: | 13 Nov 2008 16:12 |
| Reporter: | Hema Sridharan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S1 (Critical) |
| Version: | mysql-6.0-backup | OS: | Linux |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[22 Feb 2008 21:23]
Sveta Smirnova
Thank you for the report. Verified as described using following test: create database err; use err; create table t1(id int); insert into t1 values(1); create table t2(id int); --eval backup database err to '$MYSQL_TEST_DIR/var/tmp/errtry' drop database err; --eval restore from '$MYSQL_TEST_DIR/var/tmp/errtry' show databases; show tables from err; --exec head -n 2 $MYSQL_TEST_DIR/var/tmp/errtry > $MYSQL_TEST_DIR/var/tmp/errtry2 --error 1064 --eval restore from '$MYSQL_TEST_DIR/var/tmp/errtry2' show databases; show tables from err;
[26 Feb 2008 15:09]
Lars Thalmann
POSSIBLE PROBLEMS ================= 1. Failed restore should not corrupt existing database. 2. Restore should immediately fail if backup image is corrupt. 3. Errors messages shown are internal, not an error message corresponding to the RESTORE statement SOLUTION ======== 1. This is documented as a limitation. Hema will check with Peter if this "best-effort" approach is not enough. 2. There is no checksum implemented yet. 3. Should probably be fixed.
[6 Mar 2008 5:04]
Hema Sridharan
( From the email of Peter Gulutzan) I conclude that Hema's requirement "this should be fixed (FAILED RESTORE SHOULDN'T HAVE ANY EFFECT ON YOUR CURRENT DATABASE)" is not the plan, and MySQL's "best effort" is enough. Hema is right that a failed recovery is a "disaster". So MySQL should improve RESTORE ... 1. Checksums, as Lars mentioned in the Bug#34767 comment 2. RESTORE ... VALIDATE, as in Oracle RMAN i.e. run through the backup without changing anything http://www.itk.ilstu.edu/docs/Oracle/server.101/b10770/rcmsynta51.htm 3. RESTORE ... PREVIEW, though not quite as in Oracle i.e. MySQL's response from RESTORE ... PREVIEW is "I will drop the following old existing database: x. I will create the following new database: x. I will destroy x files and y objects while doing this." 4. Instead of "drop old directory then add new one", do "add new directory, then drop old directory, then rename". 5. Do not stop RESTORE when you see an error in the file. Try to skip the error and continue. This might require an additional keyword, FORCE. 6. Have a cautious mode where destruction is not possible i.e. if database directory exists it can't be dropped. E.g. RESTORE FROM 'x.bak' CAREFULLY!; will fail with error "existing database wasn't dropped". 7. Implemen non-destructive mode mentioned in worklog task descriptions. Developers are aware that failed recovery is a "disaster". So improving RESTORE should be on the roadmap.
[29 Oct 2008 9:52]
Jørgen Løland
Problem 1 - database corrupted: Best effort is not on the plan for 6.0 Problem 2 - fail immediately if corrupted image: Checksums is not on the plan for 6.0, tracked by WL#4385 Problem 3 - internal error message: Will be fixed this this bug report.
[11 Nov 2008 11:49]
Jørgen Løland
The error message for restore in the original posting: "ERROR 1064 (42000): You have an error in your SQL syntax;..." has already been changed. The error message is now: "ERROR 1677 (HY000): Could not restore table `err`.`t1`"
[13 Nov 2008 10:29]
Jørgen Løland
Since error message is ok now, there is nothing left to do. Error message was fixed in 6.0.7
[13 Nov 2008 16:12]
Paul DuBois
Cosmetic change regarding error message only; original problem still exists. No changelog entry needed.
Description: Description =========== 1)I create database and few tables in the database. 2)I backup the database to the local file. 3)I drop the database 4)Now I restore the database from the local file and I am able to see the contents of database properly 5) Now I corrupt the backed up local file (by making some changes) and attempt to restore the database again. 6) This time RESTORE fails with an error 7)But now if I check the contents of database by using "show tables from database", I see an empty set. This is not the expected result and instead I should be able to see the contents of database because, a)I did not drop any tables or database before performing RESTORE of corrupted backup image. b)The last RESTORE attempted failed with an error message. c)Existing DATABASE and tables shouldn't be affected. This behavior of restore will cause loss of data without knowing to user. How to repeat: steps to reproduce ================== 1) Create database and few tables in it. 2) Backup the database to the local file. mysql> backup database err to '/data2/hema/backup_dmp/errtry2'; +-----------+ | backup_id | +-----------+ | 5 | +-----------+ 1 row in set (0.04 sec) 3) Drop the database mysql> drop database err -> ; Query OK, 2 rows affected (0.04 sec) 4) Restore the database from local file mysql> restore from '/data2/hema/backup_dmp/errtry2'; +-----------+ | backup_id | +-----------+ | 6 | +-----------+ 1 row in set (0.17 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | TPCB | | err | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) 5)Now I see the tables in database, mysql> show tables from err; +---------------+ | Tables_in_err | +---------------+ | eht1_dt | | eht2_dtu | +---------------+ 2 rows in set (0.00 sec) 6) Now restore the database again by making some change of backup local file.(Here I corrup the backup image in local file) mysql> restore from '/data2/hema/backup_dmp/errtry2'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ': `ID` tinyint(4) NOT NULL, `Items` smallint(6) NOT NULL, `citypop` medium' at line 1 7) Now I check the contents of database, mysql> show tables from err; Empty set (0.00 sec) This leads to potential data loss despite of failed restore. Failed restore response should not corrupt the existing data. Suggested fix: If there are severe errors on restore, the system should recover by leaving the data being restored in a manageable state, that is data loss should not occur.