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

[30 Jul 2006 12:48] Jon Stephens
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.
[31 Jul 2006 14:19] MySQL Verification Team
Thank you for the bug report.
[7 Aug 2006 9:25] Mikael Ronström
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.
[7 Aug 2006 10:51] Jon Stephens
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)
[17 Aug 2006 13:16] Mikael Ronström
Reviewed by hf
[31 Aug 2006 20:41] Mikael Ronström
fixed in 5.1-kt team tree
[1 Sep 2006 7:58] Mikael Ronström
Patch will appear in 5.1.12
[2 Sep 2006 5:13] Jon Stephens
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!