Error Code: 1290. The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

by

A quick answer, that doesn’t require you to edit any configuration files (and works on other operating systems as well as Windows), is to just find the directory that you are allowed to save to using:

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.06 sec)

And then make sure you use that directory in your SELECT statement’s INTO OUTFILE clause:

SELECT *
FROM xxxx
WHERE XXX
INTO OUTFILE '/var/lib/mysql-files/report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Original answer

I’ve had the same problem since upgrading from MySQL 5.6.25 to 5.6.26.

In my case (on Windows), looking at the MySQL56 Windows service shows me that the options/settings file that is being used when the service starts is C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

On linux the two most common locations are /etc/my.cnf or /etc/mysql/my.cnf.

MySQL56 Service

Opening this file I can see that the secure-file-priv option has been added under the [mysqld] group in this new version of MySQL Server with a default value:

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.6/Uploads"

You could comment this (if you’re in a non-production environment), or experiment with changing the setting (recently I had to set secure-file-priv = "" in order to disable the default). Don’t forget to restart the service after making changes.

Alternatively, you could try saving your output into the permitted folder (the location may vary depending on your installation):

SELECT *
FROM xxxx
WHERE XXX
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.6/Uploads/report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

It’s more common to have comma seperate values using FIELDS TERMINATED BY ','. See below for an example (also showing a Linux path):

SELECT *
FROM table
INTO OUTFILE '/var/lib/mysql-files/report.csv'
    FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    ESCAPED BY ''
    LINES TERMINATED BY '\n';

More Related Contents:

  1. Error related to only_full_group_by when executing a query in MySql
  2. Find duplicate records in MySQL
  3. How to declare a variable in MySQL?
  4. Can you split/explode a field in a MySQL query?
  5. MySQL order by before group by
  6. MySQL Cannot Add Foreign Key Constraint
  7. Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)
  8. Grouping into interval of 5 minutes within a time range
  9. ERROR 1044 (42000): Access denied for user ”@’localhost’ to database ‘db’
  10. Difference between float and decimal data type
  11. Convert timestamp to date in MySQL query
  12. Update a column value, replacing part of a string
  13. Parse date in MySQL
  14. mysql dynamic query in stored procedure
  15. mysql: get record count between two date-time
  16. Table is ‘read only’
  17. How to select one row randomly taking into account a weight?
  18. auto_increment by group
  19. Get total number of rows when using LIMIT? [duplicate]
  20. MySQL Stored Procedures not working with SELECT (basic question)
  21. Table is specified twice, both as a target for ‘UPDATE’ and as a separate source for data in mysql
  22. MySQL export into outfile : CSV escaping chars
  23. Simple Random Samples from a MySQL Sql database
  24. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  25. MySQL Select minimum/maximum among two (or more) given values
  26. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  27. Select by Month of a field
  28. MySQL Illegal mix of collations
  29. delete rows from multiple tables
  30. MySQL Multiple Joins in one query?

Leave a Comment