John - I've seen people confuse MySQL users with OS users, too. I'm not doing that, and I understand the difference between MySQL privs and filesystem permissions. MySQL is running as the mysql user. I'm running the query as MySQL's root.
I am able to create partitioned or non-partitioned tables if I do not specify a data directory. I'll try your suggestion about making the filesystem perms more restrictive - I don't think I've tried that yet. Thanks for taking the time to respond. - Brad On Sat, Dec 6, 2008 at 3:42 AM, John Daisley < [EMAIL PROTECTED]> wrote: > Brad, what user are you running the mysql server as? Have you created a > mysql user and group (or another user and group) to run the server and set a > user= option in the cnf file? > > Are you absolutely sure all necessary files and directories are owned and > readable/writable only by the mysql user? Setting files and directories to > 777 will not always solve the issue and is a bug security risk, they must be > owned and readable/writable only to the mysql user. > > Are you able to create non-partitioned tables? > > This kind of problem crops up time and time again on the mysql forums and > its always filesystem permissions or people confuse mysql users with os > users or think because they log into mysql as 'root' they have root > privileges on the box. > > Regards, > > John > > What > > > On Fri, 2008-12-05 at 16:41 -0500, Brad Heintz wrote: > > > Thanks, Martin, but that's not it. As I mentioned in my email, I'm running > as MySQL root user with all priv bits set. I tried your suggestion anyway, > but no change. > > Cheers, > - Brad > > On Fri, Dec 5, 2008 at 4:34 PM, Martin Gainty <[EMAIL PROTECTED]> wrote: > > > Brad- > > > > log into mysql as admin > > GRANT FILE ON *.* to 'username'@'HOST'; > > logout > > then login to [EMAIL PROTECTED] > > retry FILE operation > > > > Martin > > ______________________________________________ > > Disclaimer and confidentiality note > > Everything in this e-mail and any attachments relates to the official > > business of Sender. This transmission is of a confidential nature and Sender > > does not endorse distribution to any party other than intended recipient. > > Sender does not necessarily endorse content contained within this > > transmission. > > > > > > > > > > > Date: Fri, 5 Dec 2008 15:44:06 -0500 > > > From: [EMAIL PROTECTED] > > > To: mysql@lists.mysql.com > > > Subject: Vexing permissions issue with partitioned CREATE TABLE > > > > > > > > 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: > > > > > > [EMAIL PROTECTED] 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 > > > [EMAIL PROTECTED] > > > > > ------------------------------ > > Send e-mail anywhere. No map, no compass. Get your Hotmail(R) account > > now.<http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_anywhere_122008> > > > > > > -- > Brad [EMAIL PROTECTED] > > > ______________________________________________ > This email has been scanned by Netintelligence > http://www.netintelligence.com/email > > -- Brad Heintz [EMAIL PROTECTED]
