Re: Vexing permissions issue with partitioned CREATE TABLE
Thanks to everyone who offered their suggestions. Those of you who suggested I look into SElinux were correct - my admin adjusted the policy and rebooted the server, and my problem is solved. I've never run across that issue before, but I'll know to look for it in the future. The list the community were a big help in pointing me in the right direction. Cheers, - Brad On Sat, Dec 6, 2008 at 6:48 AM, Per Jessen [EMAIL PROTECTED] wrote: Brad Heintz wrote: Thanks for responding. The CREATE TABLE docs for 5.1 say that DATA DIRECTORY and INDEX DIRECTORY take absolute paths (not relative), and will in fact reject paths containing the MySQL data dir. Because I'm out of other ideas, I did try creating the directories under the MySQL data dir and it doesn't change the error, so it has nothing to do with MySQL secretly expecting relative paths. I have created the directories by hand, and as I said in my original email, I've tried chown'ing them to the MySQL user. No change in outcome. If you're running SElinux or AppArmor, check the audit logs, e.g. /var/log/audit/audit.log /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brad Heintz [EMAIL PROTECTED]
Re: Vexing permissions issue with partitioned CREATE TABLE
Per - I am finding a lot of exit=-13 entries for the mysql user, but that's the same information as in the original error message. Is there anything else you're suggesting I look for? - Brad On Sat, Dec 6, 2008 at 6:48 AM, Per Jessen [EMAIL PROTECTED] wrote: Brad Heintz wrote: Thanks for responding. The CREATE TABLE docs for 5.1 say that DATA DIRECTORY and INDEX DIRECTORY take absolute paths (not relative), and will in fact reject paths containing the MySQL data dir. Because I'm out of other ideas, I did try creating the directories under the MySQL data dir and it doesn't change the error, so it has nothing to do with MySQL secretly expecting relative paths. I have created the directories by hand, and as I said in my original email, I've tried chown'ing them to the MySQL user. No change in outcome. If you're running SElinux or AppArmor, check the audit logs, e.g. /var/log/audit/audit.log /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brad Heintz [EMAIL PROTECTED]
Re: Vexing permissions issue with partitioned CREATE TABLE
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 [EMAIL PROTECTED] 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 [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 '-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
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 [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 '-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
Re: Vexing permissions issue with partitioned CREATE TABLE
That's covered, Martin. Thanks for your input. On Sat, Dec 6, 2008 at 10:45 AM, Martin Gainty [EMAIL PROTECTED] 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: [EMAIL PROTECTED] To: mysql@lists.mysql.com 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 [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 '-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
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 '-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]
Re: Vexing permissions issue with partitioned CREATE TABLE
Thanks for responding. The CREATE TABLE docs for 5.1 say that DATA DIRECTORY and INDEX DIRECTORY take absolute paths (not relative), and will in fact reject paths containing the MySQL data dir. Because I'm out of other ideas, I did try creating the directories under the MySQL data dir and it doesn't change the error, so it has nothing to do with MySQL secretly expecting relative paths. I have created the directories by hand, and as I said in my original email, I've tried chown'ing them to the MySQL user. No change in outcome. Cheers, - Brad On Fri, Dec 5, 2008 at 4:01 PM, [EMAIL PROTECTED] wrote: I've never created a partitioned table, but... $ perror 13 OS error code 13: Permission denied So I suspect some kind of file-system permissions issue... Are you sure that the path you are giving isn't relative to the mysql data dir? In which case it's trying to use something more like: /var/mysql/data/foo/ Or, perhaps, you need to do the mkdir of idx_foo for it??? Or, perhaps, MySQL really doesn't like the idea of root-owned DB files, and wants them owned by MySQL user? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brad Heintz [EMAIL PROTECTED]
Re: Vexing permissions issue with partitioned CREATE TABLE
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 '-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 Heintz [EMAIL PROTECTED]
