mysql load data infile can't get stat of file Errcode: 2

Question

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

Maximalist

Check the mysql forums. Here is just one link on it: forums.mysql.com/read.php?10,228976,228976#msg-228976 – Pandurate

@Pandurate 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 – Maximalist

Also sounds like the file may not exist: electrictoolbox.com/article/mysql/mysqlimport-errors Search for errcode: 2 on the page – Pandurate

I'm connecting remotely, and the file is on my local computer not the MySQL server computer. Would this be a problem? – Maximalist

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

I see, I didn't realize there was a LOAD LOCAL DATA INFILE – Maximalist

Answer 1

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

otherwise check if apparmor is active for your directory

Known

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 – Maximalist

it's LOAD DATA LOCAL INFILE since you lead me down the right path. Answer Accepted – Maximalist

perfect answer, i had a similar problem, and it was apparmor, thanks! :-) – Fou

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

I get the error The used command is not allowed with this MySQL version :( – Revitalize

Deprecated keyword LOCAL. Can get to work with --local startup flag, but ill advised. – Endplay

@Revitalize the fix is to start mysql with --local-infile ... credit to dba.stackexchange.com/a/55962/42273 – Virgel

Why is LOCAL sometimes needed? – Bloomer

Answer 2

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;

Adaminah

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

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 – Davidadavidde

Answer 3

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"

Mannie

Answer 4

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.

Mcquade

Answer 5

Ok, so, I have the same issue since some lib udpate (it was working like a charm before that), and so, I've tried everything I found:

• move csv file to /tmp
• try the 'local' param
• chmod to 777 the csv
• chown the csv to mysql:mysql
• check the apparmor conf (was already good)

But none of this work until I found this post (https://bbs.archlinux.org/viewtopic.php?id=227181) which I copy below for future reference (and in case the original post disappear)

The issue is that a recent change in MariaDB systemd service file prevents any file access from /home, /root, /run/user, and /tmp:

# Prevent accessing /home, /root and /run/user
ProtectHome=true

access to /tmp is restricted elsewhere in the service file. For the sake of someone else running into this issue, here are the necessary steps to allow file access from /home. Note that even if your service file is called mysqld.service (as mine is), you still want to name the folder for the supplementary service files mariadb.service.d (or it won't work).

# mkdir /etc/systemd/system/mariadb.service.d
# cd /etc/systemd/system/mariadb.service.d

• created a file called, say load_data.conf, and enter the following lines in the file:

[Service]

# Override default directive with prevents  accessing /home, /root and /run/user
ProtectHome=false

# systemctl daemon-reload
# systemctl restart mysqld

This is all documented here; https://mariadb.com/kb/en/mariadb/systemd/

(You just need to know where to look.)

Dishevel

Answer 6

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

Metatherian