Bug #19715 Using DATA/INDEX DIRECTORY with subpartitions put files in default directory
Submitted: 11 May 2006 9:14 Modified: 27 May 2006 19:07
Reporter: Horst Hunger Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.1.10 OS:Linux (Linux 9.3)
Assigned to: Mikael Ronström CPU Architecture:Any

[11 May 2006 9:14] Horst Hunger
Description:
The following SQL statement using partitions and subpartitions does not put the files in the expected directories:

CREATE TABLE t1 (
f_int1 INTEGER,
f_int2 INTEGER,
f_char1 CHAR(20),
f_char2 CHAR(20),
f_charbig VARCHAR(1000))
PARTITION BY RANGE(f_int1 DIV 2) SUBPARTITION BY HASH(f_int1) SUBPARTITIONS 2
(PARTITION parta VALUES LESS THAN (0)
DATA DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/data'
INDEX DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/index',
PARTITION partb VALUES LESS THAN (5)
DATA DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/data'
INDEX DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/index',
PARTITION partc VALUES LESS THAN (10)
DATA DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/data'
INDEX DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/index',
PARTITION partd VALUES LESS THAN (2147483646)
DATA DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/data'
INDEX DIRECTORY =
   '/data0/mysql/mysql-5.1-clone/mysql-test/var/master-data/test/index');

How to repeat:
execute the SQL statement above after creation of directories.
[11 May 2006 9:24] Horst Hunger
An addition hint (Is the directory clause simply ignored? That's not described in the Reference Manual.):
SHOW CREATE TABLE t1;
Table   Create Table
t1      CREATE TABLE `t1` (
  `f_int1` int(11) DEFAULT NULL,
  `f_int2` int(11) DEFAULT NULL,
  `f_char1` char(20) DEFAULT NULL,
  `f_char2` char(20) DEFAULT NULL,
  `f_charbig` varchar(1000) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (f_int1 DIV 2) SUBPARTITION BY HASH (f_int1) SUBPARTITIONS 2 (PARTITION parta VALUES LESS THAN (0) , PARTITION partb VALUES LESS THAN (5) , PARTITION partc VALUES LESS THAN (10) , PARTITION partd VALUES LESS THAN (2147483646) )
[11 May 2006 10:00] Hartmut Holzgraefe
DATA/INDEX DIRECTORY seem to be completely ignored, the partition files end up in the default database directory just as if no DIRECTORY specs had been given at all
[17 May 2006 8:51] Horst Hunger
As I have not found a rule where to find the files belonging to a partition, if the partition has a user defined directory and subpartitions, I suggest the following rule:
If no directory is specified for a partition, then the default directory is used for it, otherwise the specified (trivial).
If no directory is specified for a subpartition, then the directory specification of the partition is used for the subpartition, otherwise the directory specification of the subpartition (that defines the relationship between partition and subpartition in terms of using directories).
[27 May 2006 19:07] Mikael Ronström
This is a duplicate of bug #19067