How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine?

Question

I've created a file: 'C:\fff.txt' which contains data for my database table. Then I'm trying to load data from that file like this:

LOAD DATA LOCAL INFILE 'C:\\fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n'; 

but I get the following error all the time:

ERROR 2 <HY000>: File 'C:\fff.txt' not found (OS errno 2 - No such file or directory)

I've added local_infile=1 to [mysql] and [mysqld] sections of my.ini file. When I type: SHOW GLOBAL VARIABLES LIKE 'local_infile'; I can see that it's set to 'ON'. Is there a way to solve this problem?

Answer 1

Windows restricts the acces for that folder.

Best is to create a separate folder and give it the right access rights.

I tried a lot but i can't replicate your message, when the file exists it can open it and insert the data.

so the basic steps

Open your my.ini file

and find, so put there the new created folder

"c:/my_folder/"

in double quotes

# Secure File Priv. 
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/"

restart your server

place there your fff.txt

Which should look like this

test
test
test
test
test
test

and then run

LOAD DATA INFILE 'c:/my_folder/fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n'; 

and sex rows are inserted.