错误代码：1290。MySQL 服务器正在使用 --secure-file-priv 选项运行，因此无法执行此语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31951468/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
提问by MangooSaSa
I encountered such a problem: Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statementwhen I tried to execute MySQL statement (Windows):
我遇到了这样的问题：错误代码：1290。MySQL服务器运行时使用了--secure-file-priv选项，所以当我尝试执行MySQL语句时它无法执行此语句（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 xxxactually worked before I reinstalled the MySQL server.
然后它起作用了。此外，INTO OUTFILE xxx在我重新安装 MySQL 服务器之前，同样的语句实际上有效。
Anybody has ideas how to deal with this error?
有人知道如何处理这个错误吗？
回答by isedwards
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 SELECTstatement's INTO OUTFILEclause:
然后确保在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 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
在我的情况下（在 Windows 上），查看 MySQL56 Windows 服务向我显示服务启动时正在使用的选项/设置文件是 C:\ProgramData\MySQL\MySQL Server 5.6\my.ini
On linux the two most common locations are /etc/my.cnfor /etc/mysql/my.cnf.
在 linux 上，两个最常见的位置是/etc/my.cnf或/etc/mysql/my.cnf。
Opening this file I can see that the secure-file-privoption has been added under the [mysqld]group in this new version of MySQL Server with a default value:
打开这个文件我可以看到这个secure-file-priv选项已经[mysqld]在这个新版本的 MySQL Server的组下添加了一个默认值：
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 (recently I had to set secure-file-priv = ""in order to disable the default). Don't forget to restart the service after making changes.
您可以对此发表评论（如果您处于非生产环境中），或者尝试更改设置（最近我不得不进行设置secure-file-priv = ""以禁用默认设置）。更改后不要忘记重新启动服务。
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):
使用FIELDS TERMINATED BY ','. 请参阅下面的示例（还显示了 Linux 路径）：
SELECT *
FROM table
INTO OUTFILE '/var/lib/mysql-files/report.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
ESCAPED BY ''
LINES TERMINATED BY '\n';
回答by zgormez
If you changed my.iniand restarted mysqland you still get this error please check your file path and replace "\"to "/".
I solved my proplem after replacing.
如果您更改my.ini并重新启动mysql，但仍然出现此错误，请检查您的文件路径并将其替换"\"为"/". 更换后我解决了我的问题。
回答by Wieslaw Olborski
I had to set
我必须设置
C:\ProgramData\MySQL\MySQL Server 8.0/my.ini secure-file-priv=""
When I commented line with secure-file-priv, secure-file-priv was null and I couldn't download data.
当我用 注释行时secure-file-priv，secure-file-priv 为空，我无法下载数据。
回答by Svetoslav Marinov
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 ',';
