Fixing “–secure-file-priv Option” Error When Saving Selection to CSV in MySQL

MySQLpermissions

I try to save a very large select (more than 70 milion rows into csv file) on Win 2012 R2 server

I execute query as root , but I think there is still some problem with privileges

select * 
INTO OUTFILE 'D:\my_folder\my_file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from my_table
where 
...

Error

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

Thank you in advance for any help !

Best Answer

I am using MySQL Server 8.0 on Windows 10. I tried to load data from csv file using the following commands:

LOAD DATA INFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\World_cup_dataset.csv'
INTO TABLE trial
FIELDS TERMINATED BY ',';

When I run above commands, I got

"ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

I tried many methods to solve the problem. Finally, I changed the '\' characters in the file path to '/' characters.

I mean my new file path became C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/World_cup_dataset.csv and my problem were fixed. If you look at my.ini file located in the MySQL Server folder, the path of secure-file-priv is "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads". So, this solution makes sense.

Related Solutions

Mysql – how to use a different tmp dir for INTO OUTFILE select with MySQL

Assuming you have the FILE privilege, you should be able to specify a location other than the /tmp location. For example:

SELECT .. INTO OUTFILE '/home/foo/myfile.csv'

The other option, if you have access to the command line, is to use the -e switch:

> mysql -e "SELECT * FROM foo ORDER BY bar" > '/path/to/file'

HeidiSQL – How to Import CSV

As per documentation your error is:

Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS) Message: Row %ld doesn't contain data for all columns

Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition if below command doesn't work)

Try this:

LOAD DATA INFILE 'path/to/file' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id,date,date_,pow,ene,reac);

If you are interested you might want to refer this blog post which explains various use-cases for loading delimited data to mysql.

Best Answer

Related Solutions

Mysql – how to use a different tmp dir for INTO OUTFILE select with MySQL

HeidiSQL – How to Import CSV

Related Question