From: Martin Gainty Date: December 6 2008 3:45pm Subject: RE: Vexing permissions issue with partitioned CREATE TABLE List-Archive: http://lists.mysql.com/mysql/215510 Message-Id: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="_54a58c01-18fc-4229-ac35-a188e2f7d4d1_" --_54a58c01-18fc-4229-ac35-a188e2f7d4d1_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable your MySQL user will need to have GRANT FILE (as earlier suggested) Thanks Martin=20 ______________________________________________=20 Disclaimer and confidentiality note=20 Everything in this e-mail and any attachments relates to the official busin= ess of Sender. This transmission is of a confidential nature and Sender doe= s not endorse distribution to any party other than intended recipient. Send= er does not necessarily endorse content contained within this transmission.= =20 > Date: Sat=2C 6 Dec 2008 10:40:17 -0500 > From: brad.heintz@stripped > To: mysql@stripped > Subject: Re: Vexing permissions issue with partitioned CREATE TABLE >=20 > John - >=20 > I've seen people confuse MySQL users with OS users=2C too. I'm not doing > that=2C and I understand the difference between MySQL privs and filesyste= m > permissions. MySQL is running as the mysql user. I'm running the query = as > MySQL's root. >=20 > I am able to create partitioned or non-partitioned tables if I do not > specify a data directory. >=20 > I'll try your suggestion about making the filesystem perms more restricti= ve > - I don't think I've tried that yet. >=20 > Thanks for taking the time to respond. >=20 > - Brad >=20 > On Sat=2C Dec 6=2C 2008 at 3:42 AM=2C John Daisley < > john.daisley@stripped> wrote: >=20 > > Brad=2C what user are you running the mysql server as? Have you create= d a > > mysql user and group (or another user and group) to run the server and = set a > > user=3D option in the cnf file? > > > > Are you absolutely sure all necessary files and directories are owned a= nd > > 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=2C 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=2C > > > > John > > > > What > > > > > > On Fri=2C 2008-12-05 at 16:41 -0500=2C Brad Heintz wrote: > > > > > > Thanks=2C Martin=2C but that's not it. As I mentioned in my email=2C I= 'm running > > as MySQL root user with all priv bits set. I tried your suggestion any= way=2C > > but no change. > > > > Cheers=2C > > - Brad > > > > On Fri=2C Dec 5=2C 2008 at 4:34 PM=2C Martin Gainty wrote: > > > > > Brad- > > > > > > log into mysql as admin > > > GRANT FILE ON *.* to 'username'@'HOST'=3B > > > 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 recipi= ent. > > > Sender does not necessarily endorse content contained within this > > > transmission. > > > > > > > > > > > > > > > > Date: Fri=2C 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=2C > > > > `startTimeStamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00'=2C > > > > PRIMARY KEY (`id`=2C`startTimeStamp`) > > > > ) ENGINE=3DMyISAM DEFAULT CHARSET=3Dlatin1 > > > > PARTITION BY RANGE (year(startTimeStamp)) ( > > > > PARTITION p0 VALUES LESS THAN (2005) DATA DIRECTORY =3D '/foo/data_= foo' > > > > INDEX DIRECTORY =3D '/foo/idx_foo' ENGINE =3D MyISAM=2C > > > > PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY =3D > > > > '/foo/data_foo' INDEX DIRECTORY =3D '/foo/idx_foo' ENGINE =3D MyISA= M)=3B > > > > > > > > (This query is pared way down from the original=2C but still suffer= s from > > > the > > > > same problem.) Note that there are data and index directories speci= fied > > > for > > > > the individual partitions. This statement was originally generated = by a > > > > MySQL instance of version 5.1.22-rc-log=2C and I'm attempting to ru= n 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=2C because permis= sions 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=2C b= ecause > > > I'm > > > > operating as MySQL root with all privilege bits set. > > > > > > > > Other notes: > > > > - I can create the table without the DATA/INDEX DIRECTORY specified= =2C but > > > I > > > > need to place the partitions for this very large table on a separat= e > > > disk. > > > > - It shouldn't make a difference with everything set to 777=2C but = I've > > > tried > > > > setting the owner/group for the directories to root:root=2C mysql:m= ysql=2C > > > and > > > > myself=2C just to try it. No change. > > > > - The docs (if I have read everything correctly) indicate that spec= ifying > > > > DATA DIRECTORY and INDEX DIRECTORY per partition in the manner abov= e is > > > > legit=2C and that attempting to specify them at the table level for= a > > > > partitioned table is not. > > > > > > > > I've googled extensively=2C searched the list archives=2C and exhau= sted every > > > > other avenue I could think of before posting to the list=2C but am = no > > > closer > > > > to an answer. Does anyone have any ideas? Have I missed something i= n the > > > > docs? > > > > > > > > Many thanks=2C > > > > - Brad Heintz > > > > > > > > > > > > -- > > > > Brad Heintz > > > > brad.heintz@stripped > > > > > > > > ------------------------------ > > > Send e-mail anywhere. No map=2C no compass. Get your Hotmail(R) accou= nt now. > > > > > > > > > > > -- > > Brad Heintzbrad.heintz@stripped > > > > > > ______________________________________________ > > This email has been scanned by Netintelligence http://www.netint= elligence.com/email > > > > >=20 >=20 > --=20 > Brad Heintz > brad.heintz@stripped _________________________________________________________________ Send e-mail faster without improving your typing skills. http://windowslive.com/Explore/hotmail?ocid=3DTXT_TAGLM_WL_hotmail_acq_spee= d_122008= --_54a58c01-18fc-4229-ac35-a188e2f7d4d1_--