19

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.

Lateralus
  • 351
  • 1
  • 3
  • 11
  • 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!! – Sayali Sonawane Sep 20 '17 at 06: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. – Krischu Jan 22 '18 at 18:04
  • Either double blackslash or replace the backslashes with forward slashes – Brian McCall Aug 31 '18 at 21:13

4 Answers4

28

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.

Sayali Sonawane
  • 12,289
  • 5
  • 46
  • 47
  • 1
    This was very helpful. – Banago Sep 10 '18 at 19:13
  • 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. – TheSatinKnight Sep 18 '18 at 06:04
  • 1
    Gives null on my system? – William T. Mallard Feb 20 '19 at 20:51
  • 1
    Amazing! The forwardslash saved me! – Holytoutant Sep 20 '21 at 20:26
13

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';
Lateralus
  • 351
  • 1
  • 3
  • 11
  • 1
    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 – Sidhu sidharth Apr 04 '17 at 14:46
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;
Giddy Naya
  • 4,237
  • 2
  • 17
  • 30
PRITISH
  • 11
  • 1
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';
mdivk
  • 3,545
  • 8
  • 53
  • 91