CODEWITHSUNDEEP
mysqlpermissionsload-data-infile
Tim Martin
Tim Martin

Reputation: 3697

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

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?

Upvotes: 7

Views: 19563

Answers (4)

David Locke
David Locke

Reputation: 18084

I've encountered a similar problem (unable to read a file in /tmp) and adding LOCAL after LOAD DATA INFILE fixed the problem.

This Launchpad bug report might have some explanations as to why this is happening.

Upvotes: 2

Jan
Jan

Reputation: 3154

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.

Upvotes: 3

Carl Sanders
Carl Sanders

Reputation: 179

mysqlimport --local <database> <infile>

OR

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

Upvotes: 17

Joachim
Joachim

Reputation: 590

Errno 13 might be due to SELinux in case you are using a Linux server distro (for example, RedHat Enterprise Linux or CentOS). Check 'audit.log' to see if SELinux is complaining about your /tmp2 path. You can then add your path via semanage fcontext -a -t mysqld_db_t "/tmp2(/.*)?" and run restorecon -R /tmp2.

However, the solution might be much simpler and I would have replied directly under your question (instead of providing an answer), if I only knew how..

Upvotes: 1

Related Questions