MySQL Bugs: #193: Create table "table options" problems causes drop table problems

Bug #193	Create table "table options" problems causes drop table problems
Submitted:	26 Mar 2003 11:18	Modified:	19 Apr 2003 11:01
Reporter:	Germain Rioux	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.12	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
There seems to be problems associated with table options.

The "DATA DIRECTORY" and "INDEX DIRECTORY" options don't seem to be fully integrated.  Neither mysqldump nor "show create table test" show them.

It also causes problems with drop tables - the drop table does not delete the OS files stored in "DATA DIRECTORY" or "INDEX DIRECTORY" if they're differerent from the default installation locations.

Therefore, 
CREATE TABLE TEST ... DATA DIRECTORY='/DATA' INDEX DIRECTORY='/INDEX'
DROP TABLE TEST
CREATE TABLE TEST ... DATA DIRECTORY='/DATA' INDEX DIRECTORY='/INDEX'

will crash unless a user runs : 

rm /DATA/TEST.*
RM /index/TES.*

How to repeat:
create table test
( store_id int(11) NOT NULL default '0'
) TYPE=MyISAM
  DATA DIRECTORY="/data_directory"
  INDEX DIRECTORY="/index_directory"
  RAID_TYPE=RAID0

then 

drop table test

create table test
( store_id int(11) NOT NULL default '0'
) TYPE=MyISAM
  DATA DIRECTORY="/data_directory"
  INDEX DIRECTORY="/index_directory"
  RAID_TYPE=RAID0

This feature is broken.
In addition to original bug report I can add the following:

Table created with DATA DIRECTORY and INDEX DIRECTORY options:

1) Can't be changed with ALTER TABLE
Every ALTER TABLE succeeds with normal output - no errors, but table remains intact in the state it was before ALTER

2) CREATE ... DROP ... CREATE does not 'crash'. It fails with  (Errcode: 17)
# perror 17
Error code  17:  File exists

It happens because DROP removes .frm and Symlinks only, but does not dereference links to remove original .MYI and .MYD symlinks point to.

Bug was that symbolic links support was not enabled by default in 4.0.12, which caused DROP TABLE to not correctly remove the table files.

Monty already has fixed this in the 4.0 tree and will push
this shortly.

The other issues that was reported is documented in the
MySQL manual and will not be fixed in the near future
(not prioritized functionality).

In my humble opinion, the DATA DIRECTORY and INDEX DIRECTORY not being displayed in either SHOW CREATE TABLE or mysqldump is a major flaw.

It basically means that mysqldump can not be reliably used to re-generate a table to it's original state (after a backup) 

Germain Rioux