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

Question

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.

Nick · Accepted Answer · 2019-08-25 02:27:10Z

2

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.

edited Aug 25, 2019 at 2:27

Nick

147k23 gold badges67 silver badges106 bronze badges

answered Aug 25, 2019 at 2:05

Pallavi Singh

314 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

kgr · Accepted Answer · 2019-01-24 05:42:07Z

1

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?

answered Jan 24, 2019 at 5:42

kgr

7896 silver badges8 bronze badges

Collectives™ on Stack Overflow

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

Secure File Priv.

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Secure File Priv.

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related