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)
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.
LOAD DATA INFILE '/home/me/Desktop/data/myfile.csv' and both data and myfile.csv have permissions of 777The 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.
