List:General Discussion« Previous MessageNext Message »
From:John Daisley Date:December 6 2008 8:42am
Subject:Re: Vexing permissions issue with partitioned CREATE TABLE
View as plain text  
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 <mgainty@stripped> wrote:
> 
> >  Brad-
> >
> > log into mysql as admin
> > GRANT FILE ON *.* to 'username'@'HOST';
> > logout
> > then login to username@HOST
> > 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: brad.heintz@stripped
> > > To: mysql@stripped
> > > 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:
> > >
> > > [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
> >
> > ------------------------------
> > 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 Heintz
> brad.heintz@stripped
> 
> 
> ______________________________________________        
> This email has been scanned by Netintelligence        
> http://www.netintelligence.com/email


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