MySQL Bugs: #42572: Restore fails when myisampack used with -b option

Bug #42572	Restore fails when myisampack used with -b option
Submitted:	3 Feb 2009 21:39	Modified:	29 Jun 2009 14:41
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S2 (Serious)
Version:	6.0 bzr	OS:	Linux
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Create database (db2) and myisam table(t21)
Load data in table.
Execute myisampack to compress the tables along with -b option to have backup of tables datafile.
Execute myisamchk -rq to rebuild the indexes after compressing the tables.
Perform backup database operation(db2.bak)
Perform restore from db2.bak, fails by giving the message
"Could not restore database `db2`"

How to repeat:
CREATE DATABASE db2;
CREATE TABLE db2.t21(id INT NOT NULL, FN CHAR(20), INDEX(id));
INSERT INTO db2.t21 VALUES
(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'d');
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;

Note: Go to the path where these utility programs are locate and perform the following,
./myisampack -b /export/home/tmp/wl-4230p/mysql-test/var/master-data/db2/t21
./myisamchk -rq /export/home/tmp/wl-4230p/mysql-test/var/master-data/db2/t21

BACKUP DATABASE db2 TO 'db2.bak';
RESTORE FROM 'db2.bak' OVERWRITE;

mysql> backup database db2 to 'db2.bak';
+-----------+
| backup_id |
+-----------+
| 270       |
+-----------+
1 row in set (0.19 sec)

mysql> restore from 'db2.bak' overwrite;
ERROR 1683 (HY000): Could not restore database `db2`

Thank you for the report.

Verified as described with following error:

mysqltest: At line 16: query 'RESTORE FROM 'db2.bak' OVERWRITE' failed: 1683: Could not restore database `db2`

The result from queries just before the failure was:
...
Warnings from just before the error:
Error 1007 Can't create database 'db2'; database exists

Probably should be reclassified as general server bug, because if add DROP DATABASE before RESTORE I get:

mysqltest: At line 15: query 'drop database db2' failed: 1010: Error dropping database (can't rmdir './db2/', errno: 17)

Hema,

in other side after running myisampack -b there is tablename.OLD file is in the data directory, so it is expected what mysqld can not drop database. Probably this should be considered "Not a Bug"?

Thank you for the feedback.

Yes, for RESTORE this is different case. Especially because after restore fails db2 does not contain tables:

CREATE DATABASE db2;
CREATE TABLE db2.t21(id INT NOT NULL, FN CHAR(20), INDEX(id));
INSERT INTO db2.t21 VALUES
(1,'a'),(2,'b'),(3,'c'),(4,'d'),(5,'e'),(6,'f'),(7,'d');
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
INSERT INTO db2.t21 SELECT * FROM db2.t21;
Remember to run myisamchk -rq on compressed tables
- check record delete-chain
- recovering (with sort) MyISAM-table '/users/ssmirnova/build/mysql-6.0/mysql-test/var/master-data/db2/t21'
Data records: 224
- Fixing index 1
BACKUP DATABASE db2 TO 'db2.bak';
backup_id
270
RESTORE FROM 'db2.bak' OVERWRITE;
ERROR HY000: Could not restore database `db2`
show tables from db2;
Tables_in_db2

Hema, reviewers, please advice, how to fix.

The problem is as follows:

Besides of the non-informative error message, the restore fails because the database can not be re-created. It can not be re-created because it exists. It can not be dropped because of non-table files are in the database directory (here the t21.OLD file, created by myisampack -b). The error from drop is ignored.

Possible ways to fix:

1. Have a special DROP for RESTORE. It shall remove everything (unless prohibited by operating system access rights). This could be a problem for users that have important files in the database directory, for example backup files.

2. Ignore "exists already" errors from CREATE. That is, run CREATE IF NOT EXISTS implicitly. This could lead to a situation where there are more tables after RESTORE than have been at BACKUP.

3. Live with the situation, but try to give a better error report.

Ingo, please consider yet another way to fix it which is:

4. In si_objects, override the default implementation of obs::Obj::drop() method inside obs::Database_obj so that it removes all the files from the database directory prior to executing DROP DATABASE statement. The logic of obs::Database_obj::drop() could be as follows:

  a) remove all files in the database directory;
  b) call Abstract_obj::drop().

While thinking about Rafal's comment, I found another possible approach:

5. Change DROP so that it's internal function returns an error code, which can be used to decide if DROP failed due to unknown files (and no other problems). In that case we rename the directory to something unique and proceed as usual. That way we can create a new database with the old name, and the user does not lose files. A warning may be issued. This approach should work even on Windows, when one or more of the unknown files are open by the user.

Alternatives:
-------------
1) It's probably not a good idea to change the server code unless some other 
operations than restore needs it. Especially since there are good alternatives. The runtime team may be aware of other operations that would need this functionality. If there is a general need for a "power-drop", this is the best solution.
2) I don't like this.
3) A suboptimal solution. It should not be picked since there are alternatives.
4) I'm ok with this
5) I prefer this alternative, assuming that it's not a problem for MySQL to have a subdirectory that contains nothing but files MySQL does not recognize in the data directory.

I vote for solution 5. I'm also ok with 3. 

I agree that solution 4 I proposed is too dangerous as it can remove important files. I think solutions 1 and 2 are not good.

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

2815 Ingo Struewing	2009-05-19
      Bug#42572 - Restore fails when myisampack used with -b option
      
      The use of myisampack -b creates a table backup file in the
      database directory. On DROP DATABASE, this file is not
      noticed as belonging to a database object. It is not deleted.
      DROP DATABASE fails because the directory is not empty.
      
      The same situation exists if the user creates files in the
      database directory, which are not known to MySQL as belonging
      to database objects. For example backup image files.
      
      RESTORE ignored the failing DROP DATABASE. It failed when a
      CREATE DATABASE for the not dropped database failed.
      
      The problem is solved by renaming the non-empty database
      directory, if DROP DATABASE fails due to unknown files.
     @ mysql-test/suite/backup/r/backup_external.result
        Bug#42572 - Restore fails when myisampack used with -b option
        Updated test result.
     @ mysql-test/suite/backup/t/backup_external.test
        Bug#42572 - Restore fails when myisampack used with -b option
        Fixed test.
     @ sql/share/errmsg.txt
        Bug#42572 - Restore fails when myisampack used with -b option
        Added ER_RESTORE_RENAME.
     @ sql/si_objects.cc
        Bug#42572 - Restore fails when myisampack used with -b option
        Added Abstract_obj::m_errno.
        Added Database_obj::drop()

Good to push

After explanations from Ingo, I withdraw my request. Good to push.

Queued to 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/74789

2815 Ingo Struewing	2009-05-22
      Bug#42572 - Restore fails when myisampack used with -b option
      
      The use of myisampack -b creates a table backup file in the
      database directory. On DROP DATABASE, this file is not
      noticed as belonging to a database object. It is not deleted.
      DROP DATABASE fails because the directory is not empty.
      
      The same situation exists if the user creates files in the
      database directory, which are not known to MySQL as belonging
      to database objects. For example backup image files.
      
      RESTORE ignored the failing DROP DATABASE. It failed when a
      CREATE DATABASE for the not dropped database failed.
      
      The problem is solved by renaming the non-empty database
      directory, if DROP DATABASE fails due to unknown files.
     @ mysql-test/suite/backup/r/backup_external.result
        Bug#42572 - Restore fails when myisampack used with -b option
        Updated test result.
     @ mysql-test/suite/backup/t/backup_external.test
        Bug#42572 - Restore fails when myisampack used with -b option
        Fixed test.
     @ sql/share/errmsg.txt
        Bug#42572 - Restore fails when myisampack used with -b option
        Added WARN_DB_DROP_RENAMED.
     @ sql/si_objects.cc
        Bug#42572 - Restore fails when myisampack used with -b option
        Added Abstract_obj::m_errno.
        Added Database_obj::drop()

Merged to azalea June 2

No changelog entry needed. Not in any released version.

Bug http://bugs.mysql.com/bug.php?id=34067 marked as duplicate of this one.