List:General Discussion« Previous MessageNext Message »
From:Brad Heintz Date:December 5 2008 8:44pm
Subject:Vexing permissions issue with partitioned CREATE TABLE
View as plain text  
All -

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
5.1.29-rc-community.



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
total 32
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.

Other notes:
- 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
docs?

Many thanks,
- Brad Heintz


--
Brad Heintz
brad.heintz@stripped

Thread
Vexing permissions issue with partitioned CREATE TABLEBrad Heintz5 Dec
  • Re: Vexing permissions issue with partitioned CREATE TABLEFlorin Andrei6 Dec
Re: Vexing permissions issue with partitioned CREATE TABLEceo5 Dec
  • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz5 Dec
  • Re: Vexing permissions issue with partitioned CREATE TABLEPer Jessen6 Dec
    • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz7 Dec
    • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz8 Dec
Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz5 Dec
  • Re: Vexing permissions issue with partitioned CREATE TABLEJohn Daisley6 Dec
    • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz6 Dec
      • RE: Vexing permissions issue with partitioned CREATE TABLEMartin Gainty6 Dec
        • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz6 Dec
      • Re: Vexing permissions issue with partitioned CREATE TABLEBrad Heintz7 Dec