How can I get around MySQL Errcode 13 with SELECT

Question

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement. If I do:

SELECT column1, column2
INTO OUTFILE 'outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

outfile.csv will be created on the server in the same directory this database's files are stored in.

However, when I change my query to:

SELECT column1, column2
INTO OUTFILE '/data/outfile.csv'
FIELDS TERMINATED BY ','
FROM table_name;

I get:

ERROR 1 (HY000): Can't create/write to file '/data/outfile.csv' (Errcode: 13)

Errcode 13 is a permissions error, but I get it even if I change ownership of /data to mysql:mysql and give it 777 permissions. MySQL is running as user "mysql".

Strangely I can create the file in /tmp, just not in any other directory I've tried, even with permissions set such that user mysql should be able to write to the directory.

This is MySQL 5.0.75 running on Ubuntu.

Answer 1

Some things to try:

• is the secure_file_priv system variable set? If it is, all files must be written to that directory.
• ensure that the file does not exist - MySQL will only create new files, not overwrite existing ones.

Answer 2

MySQL is getting stupid here. It tries to create files under /tmp/data/.... So what you can do is the following:

mkdir /tmp/data
mount --bind /data /tmp/data

Then try your query. This worked for me after hours of debugging the issue.

Answer 3

I had the same problem on a CentOs 6.7 In my case all permissions were set and still the error occured. The problem was that the SE Linux was in the mode "enforcing".

I switched it to "permissive" using the command sudo setenforce 0

Then everything worked out for me.

Answer 4

I just ran into this same problem. My issue was the directory that I was trying to dump into didn't have write permission for the mysqld process. The initial sql dump would write out but the write of the csv/txt file would fail. Looks like the sql dump runs as the current user and the conversion to csv/txt is run as the user that is running mysqld. So the directory needs write permissions for both users.

Answer 5

Ubuntu uses AppArmor and that is whats preventing you from accessing /data/. Fedora uses selinux and that would prevent this on a RHEL/Fedora/CentOS machine.

To modify AppArmor to allow MySQL to access /data/ do the follow:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

add this line anywhere in the list of directories:

/data/ rw,

then do a :

sudo /etc/init.d/apparmor restart

Another option is to disable AppArmor for mysql altogether, this is NOT RECOMMENDED:

sudo mv /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/

Don't forget to restart apparmor:

sudo /etc/init.d/apparmor restart

Answer 6

In my case, the solution was to make every directory in the directory path readable and accessible by mysql (chmod a+rx). The directory was still specified by its relative path in the command line.

chmod a+rx /tmp
chmod a+rx /tmp/migration
etc.