7

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?

Tim Martin
  • 3,618
  • 6
  • 32
  • 43

4 Answers4

17
mysqlimport --local <database> <infile>

OR

LOAD DATA LOCAL INFILE... should fix the issue.
ajreal
  • 46,720
  • 11
  • 89
  • 119
Carl Sanders
  • 179
  • 3
  • 1
    +1 without local it is resolved in the context of the server process - so you can load data from client and from server – Unreason Dec 01 '10 at 19:55
3

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.

Jan
  • 3,044
  • 3
  • 20
  • 32
  • Was trying to do exactly the same thing, and editing AppArmor's whitelist file for mysql solved this. Thanks – Rich S Apr 28 '22 at 12:49
2

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.

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
David Locke
  • 17,926
  • 9
  • 33
  • 53
1

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..

Joachim
  • 550
  • 4
  • 9
  • For what it's worth, I'm using Ubuntu 10.04 Desktop edition, with MySQL installed from Ubuntu packages. – Tim Martin Oct 20 '10 at 05:33
  • Okay, so SELinux is not the problem there. Have you tried 'LOAD DATA LOCAL INFILE ...'? Does it give you the same result? – Joachim Oct 20 '10 at 12:08