MySQL Bugs: #35951: Backup: restore fails after directory removal

Bug #35951	Backup: restore fails after directory removal
Submitted:	9 Apr 2008 23:44	Modified:	20 Nov 2008 8:30
Reporter:	Peter Gulutzan	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.5-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
I'm using mysql-6.0-backup.
My default data directory is /usr/local/mysql/var.
My default storage engine is MyISAM.
I create a database x1, therefore /usr/local/mysql/var/x1 exists.
I create a database x2, therefore /usr/local/mysql/var/x2 exists.
I create a table x2.t with data directory /usr/local/mysql/var/x1.
I backup database x2.
I drop database x2, therefore /usr/local/mysql/var/x2 doesn't exist.
I drop database x1, therefore /usr/local/mysql/var/x1 doesn't exist.
I try to restore.
Failure.

How to repeat:
Start with "select @@datadir".
If your default directory is not /usr/local/mysql/var, then
change the literal in the CREATE TABLE statement below.

select @@datadir;
create database x1;
create database x2;
use x2
create table t (s1 char(1)) data directory '/usr/local/mysql/var/x1';
backup database x2 to '1';
drop database x2;
drop database x1;
restore from '1';

The restore will fail.

Example run:

mysql> select @@datadir;
+-----------------------+
| @@datadir             |
+-----------------------+
| /usr/local/mysql/var/ |
+-----------------------+
1 row in set (0.00 sec)

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

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

mysql> use x2
Database changed
mysql> create table t (s1 char(1)) data directory '/usr/local/mysql/var/x1';
Query OK, 0 rows affected (0.08 sec)

mysql> backup database x2 to '1';
+-----------+
| backup_id |
+-----------+
| 1         |
+-----------+
1 row in set (0.14 sec)

mysql> drop database x2;
Query OK, 1 row affected (0.00 sec)

mysql> drop database x1;
Query OK, 0 rows affected (0.00 sec)

mysql> restore from '1';
ERROR 1 (HY000): Can't create/write to file '/usr/local/mysql/var/x1/t.MYD' (Errcode: 2)

Thank you for the report.

Verified as described.

Maybe just update restriction page in the manual as it is not safe to create directories?

Also as since 5.1.24 MySQL doesn't allow to point DATA DIRECTORY to MySQL datadir used modified test to verify:

select @@datadir;
create database x1;
create database x2;
use x2;
show databases;

--exec mkdir $MYSQL_TEST_DIR/var/tmp/x1

--eval create table t (s1 char(1)) data directory '$MYSQL_TEST_DIR/var/tmp/x1'
backup database x2 to '1';
drop database x2;
drop database x1;
--exec rm -rf $MYSQL_TEST_DIR/var/tmp/x1
restore from '1';

Lars, Rafal, Chuck, Øystein, Jørgen agrees with Sveta: We should not create the missing directory.

However, the error message you get when this occurs on the current backup branch is not very helpful. It only says "1677: Could not restore table `x2`.`t`", as opposed to the error message in the comment above: "Can't create/write to file '...'"

We will wait for the new backup service interface (wl#4264). Alik will make sure it is possible to get hold of the error stack when an SQL statement fails. When the new interface is ready, the error message printed upon restore will be revised.

I just noticed that the error stack is available:

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

mysql> use x1;
Database changed
mysql> create table t (s1 char(1)) data directory '/tmp/35951';
Query OK, 0 rows affected (0.00 sec)

mysql> backup database x1 to '1';
+-----------+
| backup_id |
+-----------+
| 270       | 
+-----------+
1 row in set (0.14 sec)

mysql> restore from '1';
ERROR 1683 (HY000): Could not restore table `x1`.`t`
mysql> show warnings;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Error   |    1 | Can't create/write to file '/tmp/35951/t.MYD' (Errcode: 2) | 
| Error   | 1005 | Can't create table 'x1.t' (errno: 2)                       | 
| Warning | 1683 | Could not restore table `x1`.`t`                           | 
+---------+------+------------------------------------------------------------+
3 rows in set (0.00 sec)

************************

This is the trace I said we needed above. I think this is enough information.

Chuck agrees that there's nothing more to be done. The error messages shown above provides enough information. Closing as Won't fix