Description:
In implementing table partitioning, when specifying data directory and index directory clause in "create table", it executes properly, but there is no files generated in the specified directories at all, all files were generated in the table default directory.
How to repeat:
This is the table creation statement copied from Script:
CREATE TABLE `gps2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`datestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=265 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
/*!50100 PARTITION BY RANGE (year(datestamp) * 100 + month(datestamp))
(PARTITION p4c VALUES LESS THAN (201112) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p51 VALUES LESS THAN (201201) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN (201202) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p53 VALUES LESS THAN (201203) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p54 VALUES LESS THAN (201204) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p55 VALUES LESS THAN (201205) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p56 VALUES LESS THAN (201206) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p57 VALUES LESS THAN (201207) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p58 VALUES LESS THAN (201208) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p59 VALUES LESS THAN (201209) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p5a VALUES LESS THAN (201210) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p5b VALUES LESS THAN (201211) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION p5c VALUES LESS THAN (201212) DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB,
PARTITION pm VALUES LESS THAN MAXVALUE DATA DIRECTORY = '/opt/data/' INDEX DIRECTORY = '/opt/index/' ENGINE = InnoDB) */;
The following are files generated:
[root@dev82 test]# dir gps2*
-rw-rw---- 1 mysql mysql 8596 Dec 17 09:27 gps2.frm
-rw-rw---- 1 mysql mysql 88 Dec 17 09:27 gps2.par
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:27 gps2#P#p4c.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:27 gps2#P#p51.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p52.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p53.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p54.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p55.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p56.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p57.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p58.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p59.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p5a.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p5b.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#p5c.ibd
-rw-rw---- 1 mysql mysql 98304 Dec 17 09:35 gps2#P#pm.ibd
[root@dev82 test]# pwd
/opt/mysql_master/data/test
The following are folders the files were expected to be:
[root@dev82 opt]# dir
total 104
drwxr-xr-x 11 root root 4096 Dec 17 09:26 ./
drwxr-xr-x 24 root root 4096 Dec 16 08:03 ../
drwxr-xr-x 2 mysql mysql 4096 Dec 17 09:26 data/
drwxr-xr-x 2 mysql mysql 4096 Dec 17 09:26 index/
[root@dev82 opt]# dir data index
data:
total 16
drwxr-xr-x 2 mysql mysql 4096 Dec 17 09:26 ./
drwxr-xr-x 11 root root 4096 Dec 17 09:26 ../
index:
total 16
drwxr-xr-x 2 mysql mysql 4096 Dec 17 09:26 ./
drwxr-xr-x 11 root root 4096 Dec 17 09:26 ../
[root@dev82 opt]#
