mysql 1148进程,错误1148 MySQL此MySQL版本不允许使用命令

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

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

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

解决方案

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

Solutions:

Use --local-infile=1 argument on the mysql commandline:

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

Then the command is permitted:

Query OK, 3 rows affected (0.00 sec)

Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

Or send the parameter into the mysql daemon:

mysqld --local-infile=1

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

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

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

[mysqld]

local-infile

[mysql]

local-infile

Save the file, restart mysql. Try it again.