MySQL Bugs: #21350: Relative path for DATA/INDEX DIRECTORY causes spurious warning & file creation

Bug #21350	Relative path for DATA/INDEX DIRECTORY causes spurious warning & file creation
Submitted:	30 Jul 2006 12:48	Modified:	2 Sep 2006 5:13
Reporter:	Jon Stephens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.11 (Windows)/5.1.12-bk (Linux)	OS:	Linux (Linux/Windows)
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
If I attempt to use a relative path for INDEX DIRECTORY or DATA DIRECTORY, I get an error, and spurious .MYI and .MYD files are sometimes created in the MySQL data directory.

How to repeat:
mysql> CREATE TABLE orders_key10 (
    ->   id INT AUTO_INCREMENT,
    ->   customer_surname VARCHAR(30),
    ->   store_id INT,
    ->   salesperson_id INT,
    ->   order_date DATE,
    ->   note VARCHAR(500),
    ->   INDEX idx (id)
    -> ) ENGINE = MYISAM
    ->   PARTITION BY KEY(order_date) (
    ->     PARTITION p0
    ->       INDEX DIRECTORY = 'partition-data'
    ->       DATA DIRECTORY  = 'partition-data',
    ->     PARTITION p1,
    ->     PARTITION p2
    ->       INDEX DIRECTORY = 'partition-data'
    ->       DATA DIRECTORY  = 'partition-data',
    ->     PARTITION p3
    -> );
ERROR 1103 (42000): Incorrect table name 'partition-data'

The files partition-data.MYI and partition-data.MYD are created in the data directory.

mysql> CREATE TABLE orders_key10 (   id INT AUTO_INCREMENT,   customer_surname VARCHAR(30),   store_id INT,   salesperson_id INT,   order_date DATE,   note VARCHAR(500),   INDEX idx (id) ) ENGINE = MYISAM   PARTITION BY KEY(order_date) (     PARTITION p0       INDEX DIRECTORY = '../partition-data'       DATA DIRECTORY  = '../partition-data',     PARTITION p1,     PARTITION p2       INDEX DIRECTORY = '../partition-data'       DATA DIRECTORY  = '../partition-data',     PARTITION p3 );
ERROR 1103 (42000): Incorrect table name '../partition-data'

No files are created.

Linux: data dir is /usr/local/mysql/var, created directory /usr/local/mysql/partition-data prior to executing CREATE TABLE statements, which works with absolute path.

Windows: data dir is M:/mydata, created directory M:\mydata\partition-data prior to executing CREATE TABLE statements. (This didn't work even with an absolute path - see Bug #21349.)

Suggested fix:
Either interpret the relative path correctly, or disallow relative paths entirely. In either case, don't interpret path as a table name, or create data/index files by that name.

Thank you for the bug report.

This bug is 2 bugs. First there is a missing error check
causing the spurious files created. However there is also
a documentation bug here. If I read the manual about DATA
DIRECTORY there is no information that the path must be an
absolute path and cannot be a relative path.

This doc "bug" exists both in the partitioning section and in
the MyISAM section and in the CREATE TABLE section.

The the fact that these option require absolute paths *is* documented in the 5.1 Manual - explicitly in the portion of the narrative covering the partitioning extensions to CREATE TABLE, and implicitly in the Partitioning chapter (where only absolute paths are used for DATA DIRECTORY or INDEX DIRECTORY). 

I've changed the synopsis because the errors are:

1. Invalid warning is sometimes generated, in which case the statement itself appears to succeed; it should definitely FAIL with an unambiguous ERROR. It should NEVER say 'Query OK' (which it in these cases does).

2. "Junk" files are created by the invalid statement.

The fact that use of relative paths for these options is not *supposed* to be permitted is not in dispute. The fact that statements using them *appear* to be successful is the issue.

I see noe that my original report was not worded very well, so I wasn't very clear in this regard.

BTW, MyISAM has nothing to do with this issue that I can see:

mysql> create table rp (c INT) PARTITION BY LIST(c) (
    -> PARTITION p0 VALUES IN(1) DATA DIRECTORY='mydata');
Query OK, 0 rows affected, 1 warning (0.25 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Error | 1103 | Incorrect table name 'mydata' |
+-------+------+-------------------------------+
1 row in set (0.01 sec)

mysql> create table rp2 (c INT) engine=bdb PARTITION BY LIST(c) (
    -> PARTITION p0 VALUES IN(1) DATA DIRECTORY='mydata');
Query OK, 0 rows affected, 1 warning (0.78 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Error | 1103 | Incorrect table name 'mydata' |
+-------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> create table rp3 (c INT) engine=innodb PARTITION BY LIST(c) (
    -> PARTITION p0 VALUES IN(1) DATA DIRECTORY='mydata');
Query OK, 0 rows affected, 1 warning (0.30 sec)

mysql> show warnings;
+-------+------+-------------------------------+
| Level | Code | Message                       |
+-------+------+-------------------------------+
| Error | 1103 | Incorrect table name 'mydata' |
+-------+------+-------------------------------+
1 row in set (0.01 sec)

Reviewed by hf

fixed in 5.1-kt team tree

Patch will appear in 5.1.12

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 5.1.12 changelog. Thanks, guys!