mysql - LOAD DATA LOCAL INFILE does not work from php 5.5 using PDO

Question

I've recently moved web servers, and the new web server has a different version of PHP.

New Server: PHP 5.5.3-1ubuntu2 (cli) Old Server: PHP 5.3.10 (cli)

On the database server, my.cnf is set to allow local-infile. I can use load data local infile from:

- HeidiSQL
- The command line client running on the new web server using --local-infile=1
- PHP, using PDO, from the old web server

However, when I try to connect from the new web server, using PHP with PDO, I get: A database problem has occurred: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version

php.ini:

[MySQL]
; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysql.allow_local_infile
mysql.allow_local_infile = On

PDO constructor:

$this->db_conn = new PDO("mysql:host=$host;dbname=$dbname;port=$port", $user, $pass, array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

I've also tried ini_set('mysql.allow_local_infile', 1) and ini_set('mysql.allow_local_infile', true) in the PHP script.

The LOCAL keyword is needed because the files are hosted on the web server, not the database server.

What else does PHP 5.5 want from me?

Answer 1

You need to configure both the client and the server to allow this command:

1. Make sure the server allows LOAD DATA LOCAL INFILE. Edit /etc/my.cnf on the MySQL server:

   [server]
   local-infile=1
   

2. Set the PDO attribute in your PHP script:

   <?php
   
   $dsn = "mysql:host=localhost;dbname=test";
   $user = "root";
   $password = "root";
   $pdo = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>1));
   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   
   $pdo->exec("LOAD DATA LOCAL INFILE 'foo.csv' INTO TABLE foo FIELDS TERMINATED BY ','");
   

   This needs to be set in the driver options argument to the constructor, not in a subsequent call to setAttribute().

   I just tested the above code example successfully with PHP 5.5.8 and Percona Server 5.6.15 on CentOS Linux 6.5.

If you still have trouble, you may have a build of the MySQL client or PDO that does not permit local-infile. The requirements are described in http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html

The mysql.allow_local_infile option in your php.ini is not relevant to PDO.