MySQL Bugs: #34480: Backup: Backup to a file copies redundantly if database name is repeated.

Bug #34480	Backup: Backup to a file copies redundantly if database name is repeated.
Submitted:	12 Feb 2008 5:05	Modified:	3 Nov 2008 10:54
Reporter:	Hema Sridharan	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux
Assigned to:	Rafal Somla	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)
4) Repeat backup command but give the database name 4 times (seperated by comma) and save to a local file
5) Check the directory where the backed up file is stored. File sizes are different. (Backed up file size is actual database multiplied by number of times I repeat the command)
say for example: if the backed up file size is 1 MB for step 3 then for step 4 it is 4 MB
6) Irrespective of the redundancy with the backed up file, RESTORE command restores the database only once.

Redundant copying of the same database in the same dump (backed up file) occupies additional space and is not efficient.

How to repeat:
Create database, tables and insert few rows.

Backup database to a local file.
mysql> backup database test TO '/data2/hema/backup_dmp/test1.dmp';
+-----------+
| backup_id |
+-----------+
| 17        |
+-----------+
1 row in set (0.08 sec)

Backup database to a local file (this time give redundant database name in the command line).

mysql> backup database test  ,test,test,test,test TO '/data2/hema/backup_dmp/test2.dmp';
+-----------+
| backup_id |
+-----------+
| 18        |
+-----------+
1 row in set (0.13 sec)

Go to the path and check the backed up file (dump) size and contents.
ndbdev@ndb16:/data2/hema/backup_dmp> ls -lrt
total 24
-rw-rw----  1 ndbdev ndbdev  3466 Feb 12 04:36 test1.dmp
-rw-rw----  1 ndbdev ndbdev 16947 Feb 12 04:37 test2.dmp

Here in test2.dmp the same database is backed up for 4 times, which increases the space. It shouldn't copy the same db redundantly though the db name is given repeatedly in the command line.

Give restore command and check the database.
mysql> restore from '/data2/hema/backup_dmp/test2.dmp';
+-----------+
| backup_id |
+-----------+
| 19        |
+-----------+
1 row in set (1.38 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| general            |
| mysql              |
| test               |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

Restore works properly.

In backup command if the same database name is repeated, why to back it redundantly into the same file and therefore occupy additional space? 

Command erroring out would be ideal or copying the database only once would be efficient.

Thank you for the report.

Verified as described.

There is an earlier bug report
Bug #33567  	Backup: duplicate database names allowed
which suggests that database name repetition should be an error.

Also reported as BUG#36933.

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

2717 Rafal Somla	2008-10-24
      BUG#36933, BUG#34480, BUG#33567 (Backup: snapshot error + crash if double backup)
      
      Before: When user specified the same database twice in the BACKUP DATABASE statement, 
      it was included twice in the backup image. This lead to a host of problems.
      
      After: BACKUP DATABASE will ignore duplicates in the database list. Each database,
      together with its objects, will be included only once in the produced backup image.

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

2717 Rafal Somla	2008-10-31
      BUG#36933, BUG#34480, BUG#33567 (Backup: snapshot error + crash if double 
      backup)
      
      Before: When user specified the same database twice in the BACKUP DATABASE 
      statement, it was included twice in the backup image. This lead to a host 
      of problems.
      
      After: 
      
      1. Parser reports error if the same database repeated twice in the list (similar 
      as for other SQL statements, e.g., DROP TABLE).
      
      2. Backup kernel code will ignore duplicates in the database list (if they ever
      get there) and never try to backup the same database twice.

Fixed by BUG#36933 - now it is not possible to list the same database twice in BACKUP statement.

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

2727 Rafal Somla	2008-11-05
      BUG#36933, BUG#34480, BUG#33567 (Backup: snapshot error + crash if double 
      backup)
      
      Before: When user specified the same database twice in the BACKUP DATABASE 
      statement, it was included twice in the backup image. This lead to a host 
      of problems.
      
      After: 
      
      1. Parser reports error if the same database repeated twice in the list (similar 
      as for other SQL statements, e.g., DROP TABLE).
      
      2. Backup kernel code will ignore duplicates in the database list (if they ever
      get there) and never try to backup the same database twice.

Pushed into 6.0.9-alpha  (revid:rafal.somla@sun.com-20081105094115-zcpha090jreyw5jf) (version source revid:jorgen.loland@sun.com-20081114134411-xypyf8wyjc2nm3ly) (pib:5)