MySQL Bugs: #34579: Backup: Restore overwrites the new / modified data without warning

Bug #34579	Backup: Restore overwrites the new / modified data without warning
Submitted:	15 Feb 2008 4:05	Modified:	1 Dec 2008 17:22
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	6.0.5-alpha-debug	OS:	Linux
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
1) I created a database.
2) Created few tables and inserted few rows of data.
3) Backup the database to a local file.(Action successful)- say /tmp/DB1.dmp
4) Added few rows / columns in some of the tables and perform a commit.
5) Restore this database from the location where the backed up copy exists (say from /tmp/DB1.dmp). - Action successful.
6) Verify the data contents and meta data. Newly added entries in step 4 are lost. Restore completes without a warning to the user that the current database contents will be overwritten.

- Repeat the above test and in step-4 modify some of your table values and add new conditions (columns, rows, primary key, index etc.) These modifications are lost after RESTORE.

==> RESTORE command restores the backed up db and eventually overwrites the current database even without a warning. A customer doing redundant restore or an inadvertant restore may lose DATA.

How to repeat:
Create database - tables, add few entries.

mysql> create database abc;
Query OK, 1 row affected (0.01 sec)

mysql> use abc;
Database changed

mysql> create table t(s1 int,s2 char)engine=memory;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t values(1,'a'),(2,'b'),(3,'c'),(4,'d');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| s1   | s2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

Take backup of this database to a local file.
mysql> backup database abc to '/data2/hema/backup_dmp/abc';
+-----------+
| backup_id |
+-----------+
| 12        |
+-----------+
1 row in set (0.04 sec)

Modify the database now (add, modify the table values)

mysql> insert into t(s1, s2)values(5,'e'),(6,'f');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| s1   | s2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
|    6 | f    |
+------+------+
6 rows in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Perform a restore operation and verify the database.

mysql> restore from '/data2/hema/backup_dmp/abc';
+-----------+
| backup_id |
+-----------+
| 13        |
+-----------+
1 row in set (0.07 sec)

mysql> select * from t;
+------+------+
| s1   | s2   |
+------+------+
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
+------+------+
4 rows in set (0.00 sec)

====> RESTORE operation copies back the backed up data irrespective of the current contents. In such case user might lose data, if there are further changes to the database after the backup is taken.

RESTORE operation should give a warning message that its going to overwrite or the recent changes would be lost. A customer executing restore by mistake may lose data even without a warning.

Many thanks for writing a bug report.

Did you configure MySQL for incremental backup?

You can find more informations about this here:

http://dev.mysql.com/doc/refman/6.0/en/backup.html

Please send your my.cnf too.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

The destructive nature of RESTORE is already noted in the documentation of the RESTORE statement. I will also add mention this in the section on backup limitations.

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

2727 Jorgen Loland	2008-11-05
      Bug#34579 - Backup: Restore overwrites the new / modified data without warning
      
      Preliminary patch - should not be seen in any branch

PROPOSED SOLUTION
-----------------
Add OVERWRITE flag to RESTORE command: "RESTORE DATABASE ... OVERWRITE"

If one of the databases to be restored already exists and OVERWRITE is not specified, RESTORE command will fail with an error.

Internally, the execute_backup_command and do_restore commands will get a new bool parameter with value extracted in the parser.

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

2727 Jorgen Loland	2008-11-07
      Bug#34579 - Backup: Restore overwrites the new / modified data without warning
      
      Add OVERWRITE flag to RESTORE command
      
      Before, RESTORE would overwrite existing DBs with same name. With this patch, RESTORE will error if database exists and OVERWRITE is not specified.

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

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

2727 Jorgen Loland	2008-11-10
      Bug#34579 - Backup: Restore overwrites the new / modified data without warning
            
      Add OVERWRITE flag to RESTORE command
            
      Before, RESTORE would overwrite existing DBs with same name. With this patch, RESTORE will error if database exists and OVERWRITE is not specified.

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

2734 Jorgen Loland	2008-11-17
      Bug#34579 - Backup: Restore overwrites the new / modified data without warning
                  
      Add OVERWRITE flag to RESTORE command
                 
      Before, RESTORE would overwrite existing DBs with same name. With this patch, RESTORE will error if database exists and OVERWRITE is not specified.

Good to push.

Pushed to mysql-6.0-backup

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

2740 Jorgen Loland	2008-11-20
      Bug#34579 - Backup: Restore overwrites the new / modified data without warning
      
      Follow-up patch: Fix compile warning and cleanup iterator object in case of DBUG_RETURN

Follow-up patch good to push.

Pushed into 6.0.9-alpha  (revid:jorgen.loland@sun.com-20081120135341-7yv79zrm60xwpesz) (version source revid:jorgen.loland@sun.com-20081126084449-lq9ckif3hmxesy1n) (pib:5)

Restore syntax:

RESTORE FROM '<file>' [OVERWRITE];

Without OVERWRITE: If any of the databases to be restored already exists in the server, restore will abort with error code 1752.
With OVERWRITE: Any database that will be restored that already exists in the server will be dropped before restoring. In other words, you loose everything that was in the database in the server but not in the backup image.

-------------------------
mysql> create database t;
Query OK, 1 row affected (0.00 sec)

mysql> use t;
Database changed
mysql> create table t1 (i int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> backup database t to 't.bup';
+-----------+
| backup_id |
+-----------+
| 272       | 
+-----------+
1 row in set (0.14 sec)

mysql> insert into t1 values (2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| i    |
+------+
|    1 | 
|    2 | 
+------+
2 rows in set (0.00 sec)

mysql> restore from 't.bup';
ERROR 1752 (HY000): Database 't' already exists. Use OVERWRITE flag to overwrite.
mysql> restore from 't.bup' overwrite;
+-----------+
| backup_id |
+-----------+
| 274       | 
+-----------+
1 row in set (0.02 sec)

mysql> select * from t1;
+------+
| i    |
+------+
|    1 | 
+------+
1 row in set (0.01 sec)

Noted in 6.0.9 changelog.

Previously, RESTORE overwrote any databases with information from the
backup image. Now, RESTORE aborts with an error if the backup image
contains any databases that currently exist on the server, unless the
optional keyword OVERWRITE is given following the image filename.

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

2742 He Zhenxing	2008-12-10 [merge]
      Auto Merge