0

I'm loading a csv file into a mysql instance running on local host. I can do this using the LOAD DATA LOCAL INFILE syntax, but what I'm wondering is why I need the LOCAL if the server and file are on the same machine. Without the local, I get an error saying:

ERROR 13 (HY000): Can't get stat of '/path/to/myfile.csv' (Errcode: 13)
Jeff Storey
  • 56,312
  • 72
  • 233
  • 406
  • I'm actually voting this as a duplicate http://stackoverflow.com/questions/3971541/what-file-and-directory-permissions-are-required-for-mysql-load-data-infile – Jeff Storey Jan 09 '14 at 03:21

2 Answers2

1

That is because the system account under which MySQL is working have no rights to read this file.

When you don't specify LOCAL the file is being read directly by the MySQL server process and have to have rights to read this file.

When you add LOCAL the file is being read by mysql client program not the server process and in your case apparently has access to your csv file, which is I presume is located somewhere in the user directory of an account under which you're working.

If you put this file to a directory where MySQL process has rights to read from (e.g. /tmp/myfile.csv) LOAD DATA INFILE will work just fine.

LOAD DATA INFILE
The LOCAL keyword affects where the file is expected to be found:

  • If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.
  • If LOCAL is not specified, the file must be located on the server host and is read directly by the server.
peterm
  • 91,357
  • 15
  • 148
  • 157
  • Though I did change the permissions on the directory and data file to 777, so shouldn't this work without local? – Jeff Storey Jan 09 '14 at 03:06
  • Are you using an absolute path or relative? – peterm Jan 09 '14 at 03:09
  • I'm using an absolute path. mysql is running on my local machine. I have a folder on my desktop with permissions of 777 and I did `LOAD DATA INFILE '/home/me/Desktop/data/myfile.csv'` and both `data` and `myfile.csv` have permissions of 777 – Jeff Storey Jan 09 '14 at 03:10
  • Oh oops, I'm guessing this is AppArmor blocking it. – Jeff Storey Jan 09 '14 at 03:17
  • Yes, it did. Accepting your answer - it's close, but with the clarification of apparmor that makes it complete. – Jeff Storey Jan 09 '14 at 04:14
  • I'm glad it did. Truth to be told AppArmor is not relevant to question directly. It's the linux kernel specific thing which you BTW didn't mentioned in your question. MySQL works on different platforms. In Windows or OS X environment it could've been anti-virus or anything else. But the cause is still the same which is IMHO clearly stated in the answer - without `LOCAL` mysql server have no access to the csv file. – peterm Jan 09 '14 at 04:21
  • Fair enough. You are correct I forgot to mention it and answered the stated question. – Jeff Storey Jan 09 '14 at 04:23
0

The best place to look these up are in MySQL docs. Check out http://dev.mysql.com/doc/refman/5.1/en/load-data.html

The --local option causes mysqlimport to read data files from the client host.

na-98
  • 909
  • 8
  • 16