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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With