错误1148 MySQL此MySQL版本不允许使用的命令 [英] Error 1148 MySQL The used command is not allowed with this MySQL version

问题描述

我正在使用MySQL LOAD DATA LOCAL INFILE命令，但出现此错误:

I am using MySQL LOAD DATA LOCAL INFILE command and I get this error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1148 The used 
command is not allowed with this MySQL version: LOAD DATA LOCAL INFILE 
'/tmp/phpI0ox54' INTO TABLE `dev_tmp` FIELDS TERMINATED BY ',' ENCLOSED 
BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES; Array ( ) in 
dc_real_estate_form_submit() (line 147 of /PATH/TO/PHP/SCRIPT).

我们可以更改哪些设置以允许LOAD DATA LOCAL infile?

What setting can we change to allow LOAD DATA LOCAL infile?

这是我们正在使用的Drupal 7代码:

Here is the Drupal 7 code we are using:

$sql = "LOAD DATA LOCAL INFILE '".$file."'
    INTO TABLE `dev_tmp`
    FIELDS
        TERMINATED BY ','
        ENCLOSED BY '\"'
    LINES
    TERMINATED BY '\\r\\n'
    IGNORE 1 LINES";

db_query($sql);

推荐答案

在MySQL中加载本地文件是一种安全隐患，默认情况下处于关闭状态，如果可以的话，您希望将其保留为关闭状态.如果不允许，则会出现此错误:

Loading a local file in MySQL is a security hazard and is off by default, you want to leave it off if you can. When it is not permitted you get this error:

ERROR 1148 (42000): The used command is not allowed with this MySQL version

解决方案:

1. 在mysql命令行上使用--local-infile=1自变量:

在终端上启动MySQL时，请包含--local-infile=1参数，如下所示:

When you start MySQL on the terminal, include --local-infile=1 argument, Something like this:

mysql --local-infile=1 -uroot -p

mysql>LOAD DATA LOCAL INFILE '/tmp/foo.txt' INTO TABLE foo 
COLUMNS TERMINATED BY '\t';

然后允许该命令:

Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

或将参数发送到mysql守护程序中:

Or send the parameter into the mysql daemon:

mysqld --local-infile=1

或在my.cnf文件中进行设置(这有安全隐患):

Or set it in the my.cnf file (This is a security risk):

找到您的mysql my.cnf文件并以root用户身份对其进行编辑.

Find your mysql my.cnf file and edit it as root.

在mysqld和mysql指示符下添加local-infile行:

Add the local-infile line under the mysqld and mysql designators:

[mysqld]
local-infile 

[mysql]
local-infile 

保存文件，重启mysql.再试一次.

Save the file, restart mysql. Try it again.

更多信息可以在这里找到: http://dev.mysql. com/doc/refman/5.1/en/load-data-local.html

More info can be found here: http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html

这篇关于错误1148 MySQL此MySQL版本不允许使用的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！