16

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

26

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.

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

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.