Error 1148 MySQL The used command is not allowed with this MySQL version

Question

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

Answer 1

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:

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

2. Or send the parameter into the mysql daemon:

   mysqld --local-infile=1
   

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

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

Answer 2

In addition to using local-infile with the MySQL server (you can put this in the /etc/my.cnf file too), you also need to enable PDO to allow it:

<?php
$pdo = new PDO($dsn, $user, $password, 
    array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)
);

Otherwise it won't work, regardless of the value of local-infile on the MySQL server.