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);
Best Answer
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:
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:Then the command is permitted:
Or send the parameter into the mysql daemon:
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:Save the file, restart mysql. Try it again.
More info can be found here: http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html