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

Does Ubuntu use SELinux? Check to see if it's enabled and enforcing. /var/log/audit/audit.log may be helpul (if that's where Ubuntu sticks it -- that's the RHEL/Fedora location).

Answer 2

You can do this :

mysql -u USERNAME --password=PASSWORD --database=DATABASE --execute='SELECT `FIELD`, `FIELD` FROM `TABLE` LIMIT 0, 10000 ' -X > file.xml

Answer 3

I have same problem and I fixed this issue by following steps:

• Operating system : ubuntu 12.04
• lamp installed
• suppose your directory to save output file is : /var/www/csv/

Execute following command on terminal and edit this file using gedit editor to add your directory to output file.

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

• now file would be opened in editor please add your directory there

  /var/www/csv/* rw,

• likewise I have added in my file, as following given image :

Execute next command to restart services :

sudo /etc/init.d/apparmor restart

For example I execute following query into phpmyadmin query builder to output data in csv file

SELECT colName1, colName2,colName3
INTO OUTFILE '/var/www/csv/OUTFILE.csv'
FIELDS TERMINATED BY ','
FROM tableName;

It successfully done and write all rows with selected columns into OUTPUT.csv file...

Answer 4

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.

Answer 5

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.

Answer 6

You need to provide an absolute path, not a relative path.

Provide the full path to the /data directory you are trying to write to.