Description:
When altering a table to become further partitioned, the DATA DIRECTORY statement appears to have no effect on the placement of the partition files.
How to repeat:
mysql> create table t1 as select * from part_hold;
Query OK, 8100000 rows affected (55.78 sec)
Records: 8100000 Duplicates: 0 Warnings: 0
mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | YES | | NULL | |
| c2 | varchar(30) | YES | | NULL | |
| c3 | date | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> alter table t1
-> partition by range (year(c3))
-> (PARTITION p5 VALUES LESS THAN (2000), PARTITION p10 VALUES LESS THAN (2010));
Query OK, 8100000 rows affected (48.35 sec)
Records: 8100000 Duplicates: 0 Warnings: 0
mysql> alter table t1
-> reorganize partition p5 into
-> (PARTITION p1 VALUES LESS THAN (1996) DATA DIRECTORY='/usr/local/mysql-5.1.9-beta-linuxdata/test/d1995',
-> PARTITION p5 VALUES LESS THAN (2000));
Query OK, 0 rows affected (47.48 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(30) DEFAULT NULL,
`c3` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(c3)) (PARTITION p1 VALUES LESS THAN (1996) DATA DIRECTOR
Y = '/usr/local/mysql-5.1.9-beta-linuxdata/test/d1995' ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (2000) ENGINE = My
ISAM, PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM)
1 row in set (0.00 sec)
*** but when you check the directory specified for the p1 partition, the files are not there, but instead in the default schema directory ***
Description: When altering a table to become further partitioned, the DATA DIRECTORY statement appears to have no effect on the placement of the partition files. How to repeat: mysql> create table t1 as select * from part_hold; Query OK, 8100000 rows affected (55.78 sec) Records: 8100000 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | int(11) | YES | | NULL | | | c2 | varchar(30) | YES | | NULL | | | c3 | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> alter table t1 -> partition by range (year(c3)) -> (PARTITION p5 VALUES LESS THAN (2000), PARTITION p10 VALUES LESS THAN (2010)); Query OK, 8100000 rows affected (48.35 sec) Records: 8100000 Duplicates: 0 Warnings: 0 mysql> alter table t1 -> reorganize partition p5 into -> (PARTITION p1 VALUES LESS THAN (1996) DATA DIRECTORY='/usr/local/mysql-5.1.9-beta-linuxdata/test/d1995', -> PARTITION p5 VALUES LESS THAN (2000)); Query OK, 0 rows affected (47.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(11) DEFAULT NULL, `c2` varchar(30) DEFAULT NULL, `c3` date DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (year(c3)) (PARTITION p1 VALUES LESS THAN (1996) DATA DIRECTOR Y = '/usr/local/mysql-5.1.9-beta-linuxdata/test/d1995' ENGINE = MyISAM, PARTITION p5 VALUES LESS THAN (2000) ENGINE = My ISAM, PARTITION p10 VALUES LESS THAN (2010) ENGINE = MyISAM) 1 row in set (0.00 sec) *** but when you check the directory specified for the p1 partition, the files are not there, but instead in the default schema directory ***