error "--secure-file-priv option" when save selection to csv

Question

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 !

Answer 1

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.

Answer 2

On Win2012 I found file my.ini.

In the file there is a definition of secure_file_priv - a folder when I can download to or uplload from ,

So I change my query as following:

 SELECT * 
 INTO OUTFILE 'D:\<folder_defined_as_secure_file_priv>\my_file.csv'
 FIELDS TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\n'
 FROM my_table
 WHERE 
 ...

Answer 3

SHOW VARIABLES LIKE "secure_file_priv";

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

SELECT  *  INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/algo.csv'  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'   LINES TERMINATED BY '\n'   FROM sams.trafico;

Answer 4

Following Mehmet's answer, for me escaping the "\" character did the trick.

C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\file.csv

became

C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\file.csv

Answer 5

I have also had these issues and did some experimenting, using the different answers I found on the Internet. I have gotten this to work on my local WAMP (3.1.7) server on a Windows 10 63-bit PC. I did this within PHP, logged on as root administrator.

First I simply nullified the --secure-file-priv problem by entering secure-file-priv = "" under the [mysqld] heading in my.ini.

Secondly I created an SQL query and executed it:

$sql = "SELECT * FROM inloggning INTO OUTFILE 'C:/Temp/sample.csv' CHARACTER SET 'Latin1' FIELDS ENCLOSED BY '\"' TERMINATED BY '\;' ESCAPED BY '\"' LINES TERMINATED BY '\r\n'";

$result =   mysqli_query($databas_link, $sql) 
            or die('File output failed!(sql) - MySQL error: ('.mysqli_errno($databas_link).') '.mysqli_error($databas_link).' ');

This works perfectly for me. Now I am working on how to save the file on the web hotel server, using the same changes and syntax.

Answer 6

Let's assume you want to store the output in a subdirectory to make it easier to manage e.g. ./my_script_data/ inside the secure folder, but you might run this script on multiple servers, possibly with different secure directories.

Firstly, for each server, set the secure_file_priv to the most appropriate directory, perhaps on another drive e.g. X:/mysql_secure. Do this by modifying the my.ini file (create a backup first), searching for secure-file-priv and providing an absolute path:
secure-file-priv="X:/mysql_secure"

NOTE: Make sure to save your file in the same encoding that it was originally written in (usually UTF-8). If you do not, the MySQLxx service will not start.

In your SQL script, store your output directory in a variable, using CONCAT() to append other file paths as needed (they will have to be created manually before executing, unfortunately). That means the following path must already exist: X:/mysql_secure/my_script_data/

To create a timestamped CSV file with your output use the following:

-- ## VARIABLES
SET @stamp = DATE_FORMAT(NOW(), '%Y-%m-%d_%H%i%s');
SELECT CONCAT(@@GLOBAL.secure_file_priv, 'my_script_data/') INTO @outdir;
-- @@GLOBAL.secure_file_priv in Windows returns a path with '\'
--  hence our need to replace '\' with '/'
SET @outdir = REPLACE(@outdir, '\\', "/");

-- ## STATEMENT EXECUTION
SET @statement := CONCAT("SELECT * FROM my_database.table WHERE conditions INTO OUTFILE '",@outdir,"my_file_prefix_", @stamp, "_suffix.csv'");
PREPARE extrct FROM @statement;
EXECUTE extrct;
DEALLOCATE PREPARE extrct;

This produces a file: X:/mysql_secure/my_script_data/my_file_prefix_2021-05-24_190827_suffix.csv.

I omitted field terminations, enclosures, and line terminations because they were the default values. These could be inserted into the @statement variable if needed.

This was tested using Windows 10 and Windows Server 2016, both running MySQL 8.0.x.