ERROR 1 (HY000): Can't create/write to file (Errcode: 13 - Permission denied)

Question

I've been struggling for a couple of days with this now and have been trying to use this thread amongst other google searches to resolve it but can't seem to get it to work.

When I run this SELECT query (Ubuntu Server 15.10 & MySQL 5.6)...

SELECT column1 ,column2
FROM table 
INTO OUTFILE '/home/user/mysql_temp/file.csv';

I get this permission error...

ERROR 1 (HY000): Can't create/write to file '/home/mysql_temp/file.csv' (Errcode: 13 - Permission denied)

From my searching, I understand this to be a system permissions issue rather than a MySQL problem but I can't seem to find a solution which fixes it for me.

Checking apparmor shows that mysqld IS in enforce mode...

apparmor module is loaded.
6 profiles are loaded.
6 profiles are in enforce mode.
   /sbin/dhclient
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/lib/NetworkManager/nm-dhcp-helper
   /usr/lib/connman/scripts/dhclient-script
   /usr/sbin/mysqld
   /usr/sbin/tcpdump
0 profiles are in complain mode.
2 processes have profiles defined.
2 processes are in enforce mode.
   /sbin/dhclient (676) 
   /usr/sbin/mysqld (1285) 
0 processes are in complain mode.
0 processes are unconfined but have a profile defined.

So I edited /etc/apparmor.d/usr.sbin.mysqld and added the following lines and then restarted apparmor...

/home/user/mysql_temp/ rw,
/home/user/mysql_temp/* rw,

I also changed the owner:group of mysql_temp to mysql and gave it full permissions...

sudo chown mysql:mysql mysql_temp

...

drwxrwxrwx 2 mysql   mysql       6 Apr  1 22:02 mysql_temp

After this, I still receive the same error.

Have I missed something? Misunderstood some of the instruction? Would be grateful for advice. Thanks.

Answer 1

Not sure if this applies to your situation but I ran into similar problems on RHEL 8.

First gotcha is that systemd was configured to run the MySQL service with the privateTmp option (see https://www.freedesktop.org/software/systemd/man/systemd.exec.html#PrivateTmp=).

This means that when you write to /tmp it actually attempts to write to a directory named something like /tmp/systemd-private-<random>-mariadb.service-<random>

Secondly the directory you write to needs to be owned by mysql (and group mysql).

Lastly the SELinux context needs to be correct as well when SELinux is Enforcing.