MySQL Bugs: #39764: Datafile already exists error message even after dropping tablespace

Bug #39764	Datafile already exists error message even after dropping tablespace
Submitted:	30 Sep 2008 22:42	Modified:	9 Oct 2008 21:39
Reporter:	Hema Sridharan	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	F_TABLESPACE

Description:
I create a small test that does the following:
Creates tablespace(defined with falcon storage engine), database and some tables, backs up database, drops tablespace and performs restore. 

I tried to execute this test in a sequence with all storage engines and I always see that falcon engine doesn't clean up tablespace properly and I get the following error:
"failed: 1718: Tablespace data file 'ob' already exists"

Note: I kept this test in a suite(backup) which apparently runs against all engines taken from combinations file.
I executed like this ./mysql-test-run.pl --suite=backup falcon.test

How to repeat:
CREATE DATABASE IF NOT EXISTS objects;
CREATE TABLESPACE ts ADD DATAFILE 'ob' ENGINE=FALCON;
CREATE TABLE objects.t11(id INT)TABLESPACE ts;
--replace_column 1 #
BACKUP DATABASE objects TO 'objects1.bak';
DROP DATABASE objects;
DROP TABLESPACE ts ENGINE=FALCON;
--replace_column 1 #
RESTORE FROM 'objects1.bak';
SHOW TABLES FROM objects;
DROP DATABASE objects;
--remove_file $MYSQLTEST_VARDIR/master-data/objects1.bak

Result file
===========
CREATE DATABASE IF NOT EXISTS objects;
CREATE TABLESPACE ts ADD DATAFILE 'ob' ENGINE=FALCON;
CREATE TABLE objects.t11(id INT)TABLESPACE ts;
BACKUP DATABASE objects TO 'objects1.bak';
backup_id
#
DROP DATABASE objects;
DROP TABLESPACE ts ENGINE=FALCON;
RESTORE FROM 'objects1.bak';
backup_id
#
SHOW TABLES FROM objects;
Tables_in_objects
t11
DROP DATABASE objects;

Now when I execute the test, I get the following error:

=======================================================

TEST                           RESULT         TIME (ms)
-------------------------------------------------------

backup.falcon 'myisam'         [ pass ]            619
backup.falcon 'falcon'         [ pass ]           1800
backup.falcon 'memory'         [ fail ]

mysqltest: At line 3: query 'CREATE TABLESPACE ts ADD DATAFILE 'ob' ENGINE=FALCON' failed: 1718: Tablespace data file 'ob' already exists

Hema,  after analyzng it, I believe problem is in test case and Falcon runs ok.
doh

Notice you test fails for "memory" , i.e just after it ran with "falcon" as default engine.

In particular, in case falcon is default engine, "restore" would restore the falcon table t11 and corresponding tablespace file.  Subsequent "drop database" will remove the not remove the tablespace file, which is ok IMO (there is no 1-1 correspondence between databases and tablespaces and single tablespace can in theory have tables of different databases).

So after the test runs with falcon, "ob" file is left and this should be ok. You'll need to catch it somehow and issue another "drop tablespace"  after restore at end of the test 

In case falcon is _not_ default engine, tablespace clause is simply ignored by create database . "Restore" would restore the table but would not recreate the tablespace file, as backup file has no info about the tablespace (this is what I strongly believe, please this statement with backup guys)

Don't know what to do with it. Hema, do you think it it makes sense to change Category to backup (as you confirmed, the error is in the test case) or close as "No bug"?