How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

Question

Which particular version of Ubuntu is this and is this Ubuntu Server Edition?

Recent Ubuntu Server Editions (such as 10.04) ship with AppArmor and MySQL's profile might be in enforcing mode by default. You can check this by executing sudo aa-status like so:

# sudo aa-status
5 profiles are loaded.
5 profiles are in enforce mode.
   /usr/lib/connman/scripts/dhclient-script
   /sbin/dhclient3
   /usr/sbin/tcpdump
   /usr/lib/NetworkManager/nm-dhcp-client.action
   /usr/sbin/mysqld
0 profiles are in complain mode.
1 processes have profiles defined.
1 processes are in enforce mode :
   /usr/sbin/mysqld (1089)
0 processes are in complain mode.

If mysqld is included in enforce mode, then it is the one probably denying the write. Entries would also be written in /var/log/messages when AppArmor blocks the writes/accesses. What you can do is edit /etc/apparmor.d/usr.sbin.mysqld and add /data/ and /data/* near the bottom like so:

...  
/usr/sbin/mysqld  {  
    ...  
    /var/log/mysql/ r,  
    /var/log/mysql/* rw,  
    /var/run/mysqld/mysqld.pid w,  
    /var/run/mysqld/mysqld.sock w,  
    **/data/ r,  
    /data/* rw,**  
}

And then make AppArmor reload the profiles.

# sudo /etc/init.d/apparmor reload

WARNING: the change above will allow MySQL to read and write to the /data directory. We hope you've already considered the security implications of this.

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

I know you said that you tried already setting permissions to 777, but as I have an evidence that for me it was a permission issue I'm posting what I exactly run hoping it can help. Here is my experience:

tmp $ pwd
/Users/username/tmp
tmp $ mkdir bkptest
tmp $ mysqldump -u root -T bkptest bkptest
mysqldump: Got error: 1: Can't create/write to file '/Users/username/tmp/bkptest/people.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
tmp $ chmod a+rwx bkptest/
tmp $ mysqldump -u root -T bkptest bkptest
tmp $ ls bkptest/
people.sql  people.txt
tmp $ 

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.

This problem has been bothering me for a long time. I noticed that this discussion does not point out the solution on RHEL/Fecora. I am using RHEL and I do not find the configuration files corresponding to AppArmer on Ubuntu, but I solved my problem by making EVERY directory in the directory PATH readable and accessible by mysql. For example, if you create a directory /tmp, the following two commands make SELECT INTO OUTFILE able to output the .sql AND .sql file

chown mysql:mysql /tmp
chmod a+rx /tmp

If you create a directory in your home directory /home/tom, you must do this for both /home and /home/tom.