LOAD DATA LOCAL INFILE 给出错误 The used command is not allowed with this MySQL version
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10737974/
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
LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version
提问by Salman A
I have a PHP script that calls MySQL's LOAD DATA INFILEto load data from CSV files. However, on production server, I ended up with the following error:
我有一个调用 MySQL 的 PHP 脚本LOAD DATA INFILE来从 CSV 文件加载数据。但是，在生产服务器上，我最终出现了以下错误：
Access denied for user ... (using password: yes)
用户访问被拒绝...（使用密码：是）
As a quick workaround, I changed the command to LOAD DATA LOCAL INFILEwhich worked. However, the same command failed on client's server with this message:
作为一种快速解决方法，我更改了有效的命令LOAD DATA LOCAL INFILE。但是，相同的命令在客户端服务器上失败并显示以下消息：
The used command is not allowed with this MySQL version
此 MySQL 版本不允许使用所使用的命令
I assume this has something to do with the server variable: local_infile = offas described here.
我认为这事做与服务器变量：local_infile = off如描述在这里。
Please suggest a workaround that does not involve changing server settings. Note that phpMyAdmin utility installed on the same server appears to accept CSV files though I am not sure it it uses LOAD DATA (LOCAL) INFILE.
请提出不涉及更改服务器设置的解决方法。请注意，安装在同一台服务器上的 phpMyAdmin 实用程序似乎接受 CSV 文件，但我不确定它是否使用LOAD DATA (LOCAL) INFILE.
回答by Alvin
Ran into the same issue as root and threw me for a moment
遇到与 root 相同的问题，让我犹豫了一会儿
could be an issue with your server settings set with compile
可能是您使用 compile 设置的服务器设置的问题
to test login to console with the same user and try your load data command
使用同一用户测试登录控制台并尝试加载数据命令
if you get the same error, try closing console and running
如果您遇到相同的错误，请尝试关闭控制台并运行
mysql -u USER -p --local-infile=1 DATABASE
now try running load data command again
现在尝试再次运行加载数据命令
if it works then you're going to need to restart mysqld with command line option or re-install with configuration option
如果它有效，那么您将需要使用命令行选项重新启动 mysqld 或使用配置选项重新安装
references (references are for 5.0 but worked for me with 5.5):
参考文献（参考文献适用于 5.0，但在 5.5 中对我有用）：
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_local-infile
http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_local-infile
回答by Hernan
I found that I need to connect to database like this:
我发现我需要像这样连接到数据库：
$dbh=mysql_connect($server,$dbuser,$dbpass,false,128);
Passing 128in the flags parameter is the key.
传入128flags 参数是关键。
See http://www.php.net/manual/en/mysql.constants.php#mysql.client-flagsto read more about the flags.
请参阅http://www.php.net/manual/en/mysql.constants.php#mysql.client-flags以了解有关标志的更多信息。
回答by jcho360
take a look to this permission list, you can add them separately, IE. you can insert but not update, or you can delete but not select, etc...
看看这个权限列表，你可以单独添加它们，IE。您可以插入但不能更新，或者您可以删除但不能选择等...
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION
ALTER Enable use of ALTER TABLE
ALTER ROUTINE Enable stored routines to be altered or dropped
CREATE Enable database and table creation
CREATE ROUTINE Enable stored routine creation
CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW Enable views to be created or altered
DELETE Enable use of DELETE
DROP Enable databases, tables, and views to be dropped
EVENT Enable use of events for the Event Scheduler
EXECUTE Enable the user to execute stored routines
FILE Enable the user to cause the server to read or write files
GRANT OPTION Enable privileges to be granted to or removed from other accounts
INDEX Enable indexes to be created or dropped
INSERT Enable use of INSERT
LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES Not implemented
RELOAD Enable use of FLUSH operations
REPLICATION CLIENT Enable the user to ask where master or slave servers are
REPLICATION SLAVE Enable replication slaves to read binary log events from the master
SELECT Enable use of SELECT
SHOW DATABASES Enable SHOW DATABASES to show all databases
SHOW VIEW Enable use of SHOW CREATE VIEW
SHUTDOWN Enable use of mysqladmin shutdown
SUPER Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
TRIGGER Enable trigger operations
UPDATE Enable use of UPDATE
USAGE Synonym for “no privileges”
I think you have permision to select, delete, insert, update, but no to do other stuff,
我认为您有权选择、删除、插入、更新，但不能做其他事情，
use this command:
使用这个命令：
SHOW GRANTS
he will show you what you are able to to, in my case.
就我而言，他会告诉你你能做什么。
jcho360> show grants;
+-------------------------------------------------------+
| Grants for jbolivar@localhost |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jbolivar'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)
