错误代码:1290.MySQL服务器正在使用--secure-file-priv选项运行，因此它无法执行此语句 [英] Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

问题描述

我遇到了这样的问题:错误代码:1290.MySQL服务器正在使用--secure-file-priv选项运行，因此它无法执行此语句 当我尝试执行MySQL语句(Windows)时:

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 MySQL statement (Windows):

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

当我不执行它时:

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

然后它起作用.另外，在重新安装MySQL服务器之前，与INTO OUTFILE xxx相同的语句实际上可以正常工作.

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

有人知道如何处理此错误吗?

Anybody has ideas how to deal with this error?

推荐答案

一个快速的答案是，只需找到允许您保存到的目录:

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)

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

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'

---

原始答案

自从MySQL 5.6.25升级到5.6.26以来，我遇到了同样的问题.

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

以我为例(在Windows上)，查看MySQL56 Windows服务可知，该服务启动时使用的选项/设置文件为C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

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

在linux上，两个最常见的位置是/etc/my.cnf或/etc/mysql/my.cnf.

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

打开此文件，我可以看到在新版本的MySQL Server的[mysqld]组下添加了secure-file-priv选项，其默认值为:

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"

您可以对此进行评论(如果您处于非生产环境中)，或者尝试更改设置(最近我必须设置secure-file-priv = ""才能禁用默认设置).进行更改后，别忘了重新启动服务.

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'

更常见的是使用FIELDS TERMINATED BY ','使用逗号分隔值.参见下面的示例(还显示了Linux路径):

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

这篇关于错误代码:1290.MySQL服务器正在使用--secure-file-priv选项运行，因此它无法执行此语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！