69

I have looked all over and found no solution, any help on this would be great.

Query:

LOAD DATA INFILE '/Users/name/Desktop/loadIntoDb/loadIntoDB.csv' 
INTO TABLE `tba`.`tbl_name` 
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
field1, field2, field3
)

Error:

Can't get stat of '/Users/name/Desktop/loadIntoDb/loadIntoDB.csv' (Errcode:2)

NOTE:

I'm running MySQL Query browser on OSX 10.6.4 connecting to MySQL 5.x

Things I've tried:

  • Drag-n-drop
  • Chmod 777
  • Put in a folder with 777 permissions as well as the file having 777 permissions
Phill Pafford
  • 77,927
  • 86
  • 256
  • 378
  • Check the mysql forums. Here is just one link on it: http://forums.mysql.com/read.php?10,228976,228976#msg-228976 – spinon Aug 12 '10 at 19:52
  • @spinon tried this, doesn't work. It does give me the same file path that I have. I removed what I had and drop-n-drag the file in. gives me the same path. When executing gives me the same error – Phill Pafford Aug 12 '10 at 19:54
  • 1
    Also sounds like the file may not exist: http://www.electrictoolbox.com/article/mysql/mysqlimport-errors/ Search for errcode: 2 on the page – spinon Aug 12 '10 at 19:56
  • I'm connecting remotely, and the file is on my local computer not the MySQL server computer. Would this be a problem? – Phill Pafford Aug 12 '10 at 20:05
  • 2
    Yes. If you are running the mysql terminal from a remote machine and trying to have it pick up a local file then it isn't going to work because that path and file do not exist on the remote machine. – spinon Aug 12 '10 at 20:08
  • I see, I didn't realize there was a LOAD LOCAL DATA INFILE – Phill Pafford Aug 12 '10 at 20:30

5 Answers5

131

try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE

otherwise check if apparmor is active for your directory

Pierre
  • 31,741
  • 29
  • 101
  • 180
  • tried this but now I get an SQL error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LOCAL DATA INFILE '/Users/name/Desktop/loadIntoDb/loadIntoDB.csv' INT' at line 1 – Phill Pafford Aug 12 '10 at 20:29
  • 7
    it's LOAD DATA LOCAL INFILE since you lead me down the right path. Answer Accepted – Phill Pafford Aug 12 '10 at 20:36
  • 2
    perfect answer, i had a similar problem, and it was apparmor, thanks! :-) – RomanKonz Sep 24 '12 at 22:16
  • 7
    One things that's missing from the answer is an explanation for why LOCAL might help. The difference is whether mysql server looks for the file on the mysql server host, or if the mysql client looks for the file on it's host and passes it over to the server. I was passing a local path to a remote mysql server, which didn't work, LOCAL solves that. – Letharion May 31 '13 at 09:57
  • 9
    I get the error `The used command is not allowed with this MySQL version` :( – abbood Jan 03 '14 at 18:06
  • 2
    Deprecated keyword `LOCAL`. Can get to work with `--local` startup flag, but ill advised. – Joseph Lust Aug 30 '14 at 19:25
  • 1
    @abbood the fix is to start mysql with --local-infile ... credit to http://dba.stackexchange.com/a/55962/42273 – Zack Burt Aug 07 '15 at 17:47
  • Why is LOCAL sometimes needed? – AturSams Jun 12 '17 at 08:27
11

I had a similar problem. The resolution was a mildly ugly hack, but much easier to remember than apparmor workarounds provided that you can 'sudo'. First, I had to put the input file in the mysql sub-directory for the database I was using:

sudo cp myfile.txt /var/lib/mysql/mydatabasename

This does a copy and leaves 'root' as the file owner. After getting into mysql and doing a USE mydatabasename, I was able to populate appropriate table using

LOAD DATA INFILE 'mytabdelimitedtextfile.txt' INTO TABLE mytablename;
zkanoca
  • 8,468
  • 8
  • 42
  • 87
user2788525
  • 219
  • 2
  • 4
  • 2
    For who might find this useful, this is the solution that worked for me instead of the accepted answer. I'm on Ubuntu 12.04 LTS and MySQL 5.5.35-0ubuntu0.12.04.1. – Alejandro García Seco Jan 22 '14 at 11:33
  • 1
    Similarly, I was able to get around this on Ubuntu 12.04, mysql 5.5.31 by changing my file's user:group to mysql:mysql – user18477575 Mar 03 '14 at 08:44
2

Using --local parameter will help with this.

Example: mysqlimport --local databasename file.txt -p

source: 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"

Zodzie
  • 315
  • 1
  • 17
1

For me, copying the contents to /tmp and using that as the source folder did the trick. I use MariaDB, and my version does not allow using the "LOCAL" modifier. Interestingly, giving read-write access to the CSV folder did not work either.

jciloa
  • 719
  • 1
  • 7
  • 19
0

I had the same problem while populating a table in mysql on a AWS instance.

In my case i had the csv file in the instance itself.

Putting the Absolute path solved my problem.

Here's the line from MySQL documentation

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.

http://dev.mysql.com/doc/refman/5.7/en/load-data.html

Akash Rudra
  • 106
  • 1
  • 2