List:General Discussion« Previous MessageNext Message »
From:Brad Heintz Date:December 7 2008 4:57pm
Subject:Re: Vexing permissions issue with partitioned CREATE TABLE
View as plain text  
John -

I've chowned the pertinent directories to mysql:mysql and chmoded them to
700.  Still no change in the result.  For laughs, I tried chown and chmod
with a --reference of an existing, working data directory in /var/lib/mysql,
but again, no change.

- Brad

On Sat, Dec 6, 2008 at 10:40 AM, Brad Heintz <brad.heintz@stripped> wrote:

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



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