Thanks in advance for help with a sticky problem.
I'm attempting to create a partitioned table thus:
CREATE TABLE `my_precious_table` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`startTimeStamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`,`startTimeStamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (year(startTimeStamp)) (
PARTITION p0 VALUES LESS THAN (2005) DATA DIRECTORY = '/foo/data_foo'
INDEX DIRECTORY = '/foo/idx_foo' ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY =
'/foo/data_foo' INDEX DIRECTORY = '/foo/idx_foo' ENGINE = MyISAM);
(This query is pared way down from the original, but still suffers from the
same problem.) Note that there are data and index directories specified for
the individual partitions. This statement was originally generated by a
MySQL instance of version 5.1.22-rc-log, and I'm attempting to run it on
I get the following error when I attempt to create a table this way:
ERROR 1 (HY000): Can't create/write to file
'/foo/idx_foo/my_precious_table#P#p0.MYI' (Errcode: 13)
I'm pretty sure it's not a filesystem-level issue, because permissions on
the specified folders are all 777:
[me@mymachine foo]$ ls -al /foo
drwxrwxrwx 4 root root 4096 Dec 5 13:56 .
drwxr-xr-x 25 root root 4096 Dec 5 13:55 ..
drwxrwxrwx 2 root root 4096 Dec 5 13:56 data_foo
drwxrwxrwx 2 root root 4096 Dec 5 13:56 idx_foo
I'm also pretty sure it's not a MySQL grants/permissions issue, because I'm
operating as MySQL root with all privilege bits set.
- I can create the table without the DATA/INDEX DIRECTORY specified, but I
need to place the partitions for this very large table on a separate disk.
- It shouldn't make a difference with everything set to 777, but I've tried
setting the owner/group for the directories to root:root, mysql:mysql, and
myself, just to try it. No change.
- The docs (if I have read everything correctly) indicate that specifying
DATA DIRECTORY and INDEX DIRECTORY per partition in the manner above is
legit, and that attempting to specify them at the table level for a
partitioned table is not.
I've googled extensively, searched the list archives, and exhausted every
other avenue I could think of before posting to the list, but am no closer
to an answer. Does anyone have any ideas? Have I missed something in the
- Brad Heintz