secure-file-priv error while importing csv file in sql

0
votes

I am trying to import csv file in sql, please find the below code:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\Assignment\Auto.csv' INTO TABLE assignments.Auto FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Its giving me the error:

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 0.016 sec

I then tried with keyword LOCAL(as mentioned in some of the solutions) and it gave me error:

Error Code: 1148. The used command is not allowed with this MySQL version 0.000 sec

As i found many solution to this, so i checked the value of this variable manually in session with command:

SHOW VARIABLES LIKE "secure_file_priv";

the output for this is :

secure_file_priv C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\

**This is the same location i am using in the load data path above.

I checked the C:\ProgramData\MySQL\MySQL Server 8.0\my.ini file and it has the below details:

Secure File Priv.

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads" Can someone please tell me what is the issue here and how can i resolve this.

2
mysqlsqlmysql-workbenchload-data-infilemysqlimport

2 Answers

0
votes

If you want to use LOAD DATA LOCAL INFILE … you need to set MySQL Server global variable local_infile to true.

SET GLOBAL local_infile = true;

You need also pass --local-infle option to mysql command-line client

mysql --local-infile -hlocalhost -uroot -p

About secure file priv error - does your Auto.csv file is on the same machine where MySQL Server is running?

0
votes
SHOW VARIABLES LIKE "secure_file_priv";

This command will show the path, for example

C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/

Put your file in the same path in uploads Also check the backslash in both the path. If it is different, change in your code. I did the two steps and it got resolved in MySQL 8.