MySQL import csv file ERROR 13 (HY000): Can't get stat of /path/file.csv (Errcode: 2)

Question

I am an absolute beginner to MySQL (5.5.34) / Linux (Ubuntu 12.04 LTS)`

I have created a simple database with one table. When trying to import data into it via a 'data_test.csv file an error message 13 appears.

Exert from Terminal follows:

mysql> source /home/g/stockrecdb/load_test.sql;
Database changed
ERROR 13 (HY000): Can't get stat of '/home/stockrecdb/data_test.csv' (Errcode: 2)
mysql> 

Note: if using LOCAL in LOAD DATA LOCAL INFILE an error appears: ERROR 1148 (42000): The used command is not allowed with this MySQL version

Answer 1

I hope you are using LOAD DATA INFILE.

Try to use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE.

Other issue might be this, please visit the following links : MySQL LOAD DATA.

When you login in MySQL do like below,

abdul@xmpp3:~/Desktop/Jiva$ mysql -uroot -p --local-infile
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 257
Server version: 5.5.29-0ubuntu0.12.04.1-log (Ubuntu)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use mydb;


mysql> LOAD DATA LOCAL INFILE '/home/abdul/Desktop/STATISTIC_T.csv' INTO TABLE STATISTIC_T  FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n'  IGNORE 1 LINES;

Now load your CSV using LOAD DATA LOCAL INFILE ,we need to use --local-infile before we load CSV in in new MySQL vesrsions, due to security reasons.

Answer 2

MySQL requires the CSV file to be in a directory it can access.

Move your file from "/home/stockrecdb/" to "/tmp" may also solve the problem.

Answer 3

Although the above answer solved the problem for the OP, I feel I should contribute regarding how I solved the same problem. Error 13 is caused by incorrect or insufficient file permissions. In my case this was down to SELinux. If you are using SELinux, the fix for adding your data file path to the permitted directories for MySQL can be found in this answer: https://stackoverflow.com/a/3971632/1449160

Answer 4

I had this exact error message Error 13 (HY000) which was solved using the LOCAL option suggested by Abdul Manaf. However, I got stuck with a CSV file of size 101GB. While trying to import this CSV file, the system reported LOW Disk Space Warning and the import task was not finishing. Frustratingly, the disk utility Gui tools even df -h command showed that my root directory had more than 85GB of disk space left.

Then I learned that the LOCAL option copies the CSV file to a temporary place in the root / directory. Then it reads from the temporary file. Since my CSV file of 101GB was bigger than the empty space left on my root / I was seeing the low disk warning, however, this was not detected in many GUI and command-based tools except the system monitor which I recognized later. Since I am working on a MySQL database of more than 300GB, I had to move the MySQL data directory to another internal disk and I was importing the CSV from an external hard disk. Copying the 101GB CSV file into the MySQL data directory in the internal disk let me run the LOAD DATA INFILE command without any error message.

So be aware of LOCAL option for larger CSV files greater than the free space of your root directory. LOCAL option is also not a good idea for SSD's endurance.

Answer 5

Another cause for the "Error 13 Can't Stat " is that the target file is not within the /var/lib/mysql directory hierarchy. Apparently the mysql server approaches the file system quite differently than a normal user.

I was trying to load/update a table with:

LOAD DATA INFILE '/home/pi/sites.csv'

and MySQL (MariaDB actually) would barf with Error 13 no matter what I did to permissions. Annoyingly, it would give this Permission Denied error even if the the file didn't exist, which was sufficient clue for me to remember that on a Win10 installation, the sites.csv source was in an Upload folder in the MySql hierarchy.

Once I moved sites.csv into /var/lib/mysql/ens2_0 and referenced it without an absolute path, it worked a charm.

Answer 6

Supposedly for Ubuntu, Mint, or other distributions using Apparmour, allowable file paths and directories, can be edited within configuration file located for me at /etc/apparmor.d/usr.sbin.mysqld.

However, I still got the error 13, unless I included the LOCAL parameter before INFILE.