Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL exporting INTO OUTFILE --secure-file-priv error when using set directory

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.

like image 513
Lateralus Avatar asked Nov 13 '15 10:11

Lateralus


People also ask

How do you fix error code 1290 the MySQL server is running with the -- secure file priv option so it Cannot execute this statement?

Fixing the MySQL error 1290 by reconfiguring and restarting Go to start menu and type services. msc then press Ctrl+Shift+Enter to run it as an administrator. Locate the MySQL service and double-click to open its properties dialog. Check the Path to Executable for the –defaults-file option to determine where my.

How do I change the secure file priv in MySQL workbench?

The secure_file_priv value is a read-only value, so you can't change it directly using SQL query. To change the value of secure_file_priv variable, you need to create a MySQL configuration file that sets the value of the variable under [mysqld] options.


1 Answers

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.

like image 105
Sayali Sonawane Avatar answered Oct 11 '22 13:10

Sayali Sonawane