List:General Discussion« Previous MessageNext Message »
From:Brad Heintz Date:December 6 2008 3:53pm
Subject:Re: Vexing permissions issue with partitioned CREATE TABLE
View as plain text  
That's covered, Martin.  Thanks for your input.

On Sat, Dec 6, 2008 at 10:45 AM, Martin Gainty <mgainty@stripped> wrote:

>  your MySQL user will need to have GRANT FILE (as earlier suggested)
>
> Thanks
> 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: Sat, 6 Dec 2008 10:40:17 -0500
> > From: brad.heintz@stripped
> > To: mysql@stripped
> > Subject: Re: Vexing permissions issue with partitioned CREATE TABLE
>
> >
> > 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 <
> > john.daisley@stripped> 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 <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 Heintzbrad.heintz@stripped
> > >
> > >
> > > ______________________________________________
> > > This email has been scanned by Netintelligence
> http://www.netintelligence.com/email
> > >
> > >
> >
> >
> > --
> > Brad Heintz
> > brad.heintz@stripped
>
> ------------------------------
> Send e-mail faster without improving your typing skills. Get your Hotmail(R)
>
> account.<http://windowslive.com/Explore/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_speed_122008>
>



-- 
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