MySQL permissions issue - should be non-issue

Question

This is making me kind of crazy: I did a mysqldump of a partitioned table on one server, moved the resulting SQL dump to another server, and attempted to run the insert. It fails, but I'm having difficulty figuring out why. Google and the MySQL forums and docs have not been much help.

The failing query looks like this (truncated for brevity and clarity, names changed to protect the innocent):

CREATE TABLE `my_precious_table` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `somedata` varchar(20) NOT NULL,
 `aTimeStamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
 PRIMARY KEY (`id`,`aTimeStamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DATA DIRECTORY='/opt/data/data2/data_foo/' INDEX DIRECTORY='/opt/data/data2/idx_foo/' 
/*!50100 PARTITION BY RANGE (year(aTimeStamp)) SUBPARTITION BY HASH ( TO_DAYS(aTimeStamp)) 
(PARTITION p0 VALUES LESS THAN (2007) (SUBPARTITION foo0 DATA DIRECTORY = '/opt/data/data2/data_foo' INDEX DIRECTORY = '/opt/data/data2/idx_foo' ENGINE = MyISAM), 
PARTITION p1 VALUES LESS THAN (2008) (SUBPARTITION foo1 DATA DIRECTORY = '/opt/data/data2/data_foo' INDEX DIRECTORY = '/opt/data/data2/idx_foo' ENGINE = MyISAM), 
PARTITION p2 VALUES LESS THAN (2009) (SUBPARTITION foo2 DATA DIRECTORY = '/opt/data/data2/data_foo' INDEX DIRECTORY = '/opt/data/data2/idx_foo' ENGINE = MyISAM), 
PARTITION p3 VALUES LESS THAN MAXVALUE (SUBPARTITION foo3 DATA DIRECTORY = '/opt/data/data2/data_foo' INDEX DIRECTORY = '/opt/data/data2/idx_foo' ENGINE = MyISAM)) */;

The error is:

ERROR 1 (HY000): Can't create/write to file '/opt/data/data2/idx_foo/my_precious_table#P#p0#SP#foo0.MYI' (Errcode: 13)

"Can't create/write to file" looked like a permissions issue to me, but permissions on the targeted folders look thus:

drwxrwxrwx 2 mysql mysql 4096 Dec  1 16:24 data_foo
drwxrwxrwx 2 mysql mysql 4096 Dec  1 16:25 idx_foo

For kicks, I've tried chowning to root:root and myself. This did not fix the issue.

Source MySQL server is version 5.1.22-rc-log. Destination server is 5.1.29-rc-community. Both are running on recent CentOS installations.

Edit: A little more research shows that Errcode 13 is, in fact, a permissions error. But how can I get that on rwxrwxrwx?

Edit: Bill Karwin's excellent suggestion didn't pan out. I'm working as the root user, and have all privilege flags set.

Edit: Creating the table WITHOUT specifying data directories for the individual partitions works - but I need to put these partitions on a larger disk than the one on which this MySQL instance puts tables by default. And I can't just specify the DATA/INDEX DIRECTORY at the table level - that's not legit in the version of MySQL I'm using (5.1.29-rc-community).

Edit: Finally came across the answer, thanks to the MySQL mailing list and internal IT staff. See below.

Answer 1

http://bugs.mysql.com/bug.php?id=19557

You will also receive an error message of the MySQL user ID running the query does not have "DATA FILE" privileges that allows the user ID to write to the file system.

In other words, it can be a permission problem with respect to SQL privileges, not operating system file permissions.

Answer 2

It turned out to be an SElinux issue - all my filesystem permissions were fine, but there was a higher-level policy set against MySQL accessing that disk partition.

Lesson: When you have a permissions issue but ownership and filesystem permissions are obviously correct, look to SElinux.

Answer 3

Hi,

Definitely agree on the thread title, storing a table on a non-default location should be a NON-ISSUE ;-)

Went through the same set of hurdles (permissions, grants etc) with the same result, i.e. :

ERROR 1 (HY000): Can't create/write to file '/home/mysql/tb1.MYD' (Errcode: 13)

Note that /home/mysql is owned by mysql.mysql and 700-moded:

root@:/home# ll

total 2 drwxr-xr-x 2 root root 1520 2009-06-01 17:41 log

drwxr-xr-x 2 ezio ezio 48 2009-05-21 08:57 mnt

drwx------ 2 mysql mysql 48 2009-06-02 14:37 mysql

drwxr-xr-x 2 ezio ezio 80 2009-05-31 20:28 tmp

drwxr-xr-x 6 ezio ezio 160 2009-05-21 10:02 var

drwxr-xr-x 7 www-data www-data 368 2009-05-28 13:10 www

drwxr-xr-x 9 xymon xymon 424 2009-05-22 20:57 xymon

I'm running the CREATE TABLE sql with "root" mysql user which I granted the FILE privilege, like this:

mysql> grant file on *.* to root@localhost; Query OK, 0 rows affected (0.00 sec)

And, last but not least ... I'm not running SELinux but Ubuntu server 09-04.

What next? How to get out of this cul-de-sac?

Thanks,

      Ezio

Answer 4

Hi,

I tried the whole story on a different system, same mix of users and file permissions but different mysql versions:

This one works:

ezio@cevrin:~$ mysql --version

mysql Ver 14.12 Distrib 5.0.45, for pc-linux-gnu (i486) using readline 5.2

This one doesn't:

ezio@fotubuntu:~$ mysql --version

mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

Is is a matter of version or something else I'm missing?

Ezio

Answer 5

On Ubuntu look into the apparmor settings for mysql

vi /etc/apparmor.d/usr.sbin.mysql

This should solve the permission issues. For a quick test you can even try

/etc/init.d/apparmor stop

But don't forget to restart the service.

This took me some time to figure out. And after reading "SELinux" it was clear that I have forgotten this new kind of protection on Ubuntu.