MySQL LOAD DATA INFILE "not found (Errcode: 13 - Permission denied)"

Question

Last week this was working, and today it isn't. I have not changed the php file. All I have done in the interim is run apt-get update on my Ubuntu 15.04 server.

The MySQL statement I have is:

LOAD DATA INFILE "/var/www/html/uploads/TitleList.csv" INTO TABLE tblLSITitleList FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\"" IGNORE 5 LINES;

This gives the following error:

File '/var/www/html/uploads/TitleList.csv' not found (Errcode: 13 - Permission denied)

I have tried the following solution (which was how I solved this exact problem early last week: LOAD DATA INFILE Error Code : 13

I have checked the apparmor and the uploads folder is still there.

I have also tried chmodding various permissions, including 777, but nothing helps.

Any clever suggestions?

Answer 1

Adding the LOCAL keyword worked for me. I also made sure that the file has READ permission, since I'm working in Ubuntu:

LOAD DATA LOCAL INFILE '/path/to/file/name.csv'
    INTO TABLE 'table_name'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';

Answer 2

While one could copy the CSV into the mysql data directory, however this is not a good on-going solution. If you're using SQL command-line or code, include the LOCAL option into the query.

• For Example: Instead of LOAD DATA INFILE try LOAD DATA LOCAL INFILE

If you're using cPanel or phpmyadmin to import the CSV using LOAD DATA then be sure to Enable Use LOCAL keyword. This worked for me in a shared server environment.

This should prevent one having to copy the CSV to the MySQL data directory each time.

Answer 3

In my case, LOAD DATA INFILE is a success with the first dataset. But with the second dataset, I've got this errcode 13.

What I did (win user), I press CTRL+ALT+DELETE and then I selected Start Task Manager, then I went to the processes tab and selected Excel then pressed End Task.

Then tried again the LOAD DATA INFILE and it worked.

Note: Before this, I tried to open the CSV file just to check the dataset. It took too long, therefore, I closed it immediately before the file opened.

Maybe the incomplete process disturbed the load process.

I'm not sure with PHP because I just using phpmyadmin and play with MySQL

Answer 4

From the docs:

For security reasons, when reading text files located on the server, 
the files must either reside in the database directory or be 
readable by all

I recommend copying your .csv file to database directory, something like

cp TitleList.csv /var/lib/mysql/yourdbname/TitleList.csv

and then

LOAD DATA INFILE 'TitleList.csv' INTO etc ...