错误代码：1290。MySQL服务器使用--secure-file-priv选项运行，因此无法执行此语句

I encountered such a problem: Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when I tried to execute my sql statement (Windows):

我遇到了这样的问题：错误代码：1290。MySQL服务器运行时带有--secure-file-priv选项，因此当我尝试执行我的sql语句时，它无法执行此语句（Windows）：

SELECT *
FROM xxxx
WHERE XXX
INTO OUTFILE 'report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

When I execute it without:

当我执行它时：

INTO OUTFILE 'report.csv'
    FIELDS TERMINATED BY '#'
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Then it works. Also, the same statement with INTO OUTFILE xxx actually works before I reinstalled the MySQL server.

然后它工作。此外，在重新安装MySQL服务器之前，与INTO OUTFILE xxx相同的声明实际上也有效。

Anybody has ideas how to deal with this error?

有人有想法如何处理这个错误？

3 个解决方案

#1

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:

快速回答，不要求您编辑任何配置文件（并且可以在其他操作系统和Windows上运行），只需找到允许保存的目录：

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 statements' INTO OUTFILE clause:

然后确保在SELECT语句的INTO OUTFILE子句中使用该目录：

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.

自从从MySQL 5.6.25升级到5.6.26后，我遇到了同样的问题。

In my case (on Windows), looking at the MySQL56 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

在我的情况下（在Windows上），查看MySQL56服务向我显示服务启动时使用的选项/设置文件是C：\ ProgramData \ MySQL \ MySQL Server 5.6 \ my.ini

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

打开这个文件，我可以看到在这个新版本的MySQL服务器中添加了secure-file-priv选项，默认值为：

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

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. 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'

#2

If you changed my.ini and restarted mysql and you still get this error please check your file path and replace "\" to "/". I solved my proplem after replacing.

如果您更改了my.ini并重新启动了mysql但仍然出现此错误，请检查您的文件路径并将“\”替换为“/”。我在更换后解决了问题。

#3

-3

The code above exports data without the heading columns which is weird. Here's how to do it. You have to merge the two files later though using text a editor.

上面的代码导出的数据没有标题列，这很奇怪。这是怎么做的。您必须稍后使用文本编辑器合并这两个文件。

SELECT column_name FROM information_schema.columns WHERE table_schema = 'my_app_db' AND table_name = 'customers' INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.6/Uploads/customers_heading_cols.csv' FIELDS TERMINATED BY '' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ',';

#1