20

I'm trying to run the following on a MySQL database:

SELECT * FROM mysql.db INTO OUTFILE "C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\db.csv" FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n';

I get the error:

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

When I run the following:

mysql> SELECT @@secure_file_priv;

I get:

+------------------------------------------------+ | @@secure_file_priv | +------------------------------------------------+ | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ | +------------------------------------------------+

So why is it not exporting the file even though I am using the set --secure-file-priv location?

I am used to MSSQL and new to MySQL.

Improve this question
3
  • 1
    it is not just bcz of this path typo, if you will try to dump file at different path location, still you will get this error. I used SELECT @@secure_file_priv; to find out path location for my machine and then used it to dump file. Thank you!! Commented Sep 20, 2017 at 6:10
  • I had the same problem because of --secure-file-priv being set. But the following syntax worked for me: mysql> select * from sometable into outfile 'c:\\ProgramData\\MySQL\\MySQL Server 5 .7\\Uploads\\out.txt'; Clue is to use double backslash. Commented Jan 22, 2018 at 18:04
  • Either double blackslash or replace the backslashes with forward slashes Commented Aug 31, 2018 at 21:13

4 Answers 4

Reset to default
29

It is important to use path location mentioned in:

mysql> SELECT @@secure_file_priv;

If you will use customized path location, you will still get this error. As mentioned by Lateralus, don't forget to change path to forward slashes.

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

4 Comments

This was very helpful.
Once you have this value, you must specify the full path and filename to write the file. It's not like other systems that assume a "home directory", it's security feature that requires you to KNOW the directory or the attempt will fail. Of course, this is somewhat defeated since anyone hacking at that level would likely be able to look up the default value for your distro, but it's something. And they'd still require access to that location to get the data file or execute code from it.
Gives null on my system?
Amazing! The forwardslash saved me!
15

Argh. It was a freakin' typo, my \'s should have been /'s

So my query is now this:

SELECT * FROM mysql.db INTO OUTFILE "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/db_pipe.csv" FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Improve this answer

1 Comment

am getting the same error and the file path has already forward slashes. I am trying to connect to sql on another server from my local. So where should i change the configurations? Have tried creating the same path on local (windows) but its not working
1

This worked for me. It needs double backslash and if you are trying any tools in between to connect to mysql simply add escape chars.

SELECT * INTO OUTFILE 'C:\\\\ProgramData\\\\MySQL\\\\MySQL Server 8.0\\\\Uploads\\\\employees.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM employees;
Improve this answer

Comments

1

In my case (Windows):

  1. In my.ini, set secure_file_priv=""

  2. Use double back slash in the path like below:

SELECT description, comment FROM usecase INTO OUTFILE 'C:\\tmp\\usecase0.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.