What file and directory permissions are required for MySQL LOAD DATA INFILE?

Question

I have a script that is trying to load some data into MySQL with LOAD DATA INFILE. For some reason, it works if the file is in the /tmp directory, but not if the file is in another directory with identical permissions. I can't find any way to get MySQL to import data from outside the /tmp directory, or the database directory, but I can't find anything in the manual that explains why this would be the case.

The situation:

$ ls -l /
...
drwxrwxrwt  21 root root  4096 2010-10-19 20:02 tmp
drwxrwxrwt   2 root root  4096 2010-10-19 20:14 tmp2

$ ls -l /tmp/data.csv 
-rwxr-xr-x 1 timm timm 415431 2010-10-19 20:02 /tmp/data.csv

$ ls -l /tmp2/data.csv 
-rwxr-xr-x 1 timm timm 415431 2010-10-19 20:14 /tmp2/data.csv

AFAICT these are identical in the important respects. However, if at the MySQL command line I do:

> LOAD DATA INFILE '/tmp2/data.csv' IGNORE INTO TABLE ports
      FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';
ERROR 29 (HY000): File '/tmp2/data.csv' not found (Errcode: 13)

> LOAD DATA INFILE '/tmp/data.csv' IGNORE INTO TABLE ports 
      FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ';
Query OK, 1 row affected, 1 warning (0.04 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

I gather from forum postings that errno 13 indicates a permission problem. It seems that /tmp is treated specially by MySQL, but why? The closest I can come is a line in the manual saying:

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all.

/tmp isn't in the database directory, but maybe it gets treated as if it is. So how should I set things up in order for it to read files outside of /tmp?

Answer 1

mysqlimport --local <database> <infile>

OR

LOAD DATA LOCAL INFILE... should fix the issue.

Answer 2

Errcode 13 means missing permissions (in contrast to Errcode 2, which means the file does not exist). MySQL needs the file to be readable by anyone. THe permissions of your files are fine.

We once had the same issue on a CentOS server and it was caused by AppArmor which forbid the MySQL application to access files not being listed in a whitelist in /etc/ap­par­mor.d/usr.​sbin.​mysqld. Maybe you have some kind of security suit which causes a similar behavior?

As mentioned by others, using LOAD DATA INFILE LOCAL can be a workaround.