I am trying to use
LOAD DATA INFILE to insert some records into a table. Unfortunately, it's not working.
Here are some details
If I use this instruction:
LOAD DATA INFILE 'file.txt' INTO TABLE table_ex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3, field4);
It works using the MySQL client program and a PHP application. In this way it will look for the file in the Data Directory of my MySQL installation.
Now if I try to execute the instructions using the
LOCAL option, it only works if I use the mysql client, but not from PHP:
LOAD DATA LOCAL INFILE 'path/to/file/file.txt' INTO TABLE table_ex FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, field2, field3, field4);
Again.. it works with MySQL client but not from the PHP application... I get this error:
LOAD DATA LOCAL INFILE forbidden in /path/to/my/application
I read that the problem is related to the compilation of PHP and using mysqlnd. I am using PHP 5.3.8 and MySQL 5.5.15, but I haven't found a solution.
Additional information: until now the only help I've found was an open PHP bug:
Check docs http://php.net/manual/en/ref.pdo-mysql.php.
Basically you need:
PDO::MYSQL_ATTR_LOCAL_INFILE => true
Set at instantiation.
$conn = new \PDO("mysql:host=$server;dbname=$database;", "$user", "$password", array( PDO::MYSQL_ATTR_LOCAL_INFILE => true, ));
had this problem today and solved it by setting the following in php.ini
mysqli.allow_local_infile = On
Easier work around is to use exec()
exec("mysql -u myuser -pMyPass -e \"USE mydb;TRUNCATE mytable;LOAD DATA INFILE '" . $file . "' IGNORE INTO TABLE mytable;\"; ");
According to the MySQL manual MySQL must be compiled with
--enable-local-infile. From a comment at that link:
You MUST have compiled PHP using the full path to MySQL, otherwise it will use it's internal handlers, which don't work with the "new" LOAD DATA.
--with-mysql=/usr/local/mysql (assuming your MySQL is located here)
You MUST start the MySQL daemon with the option '--local-infile=1'
I didn't get the exact error you get, but you need no ensure the following:
Enable by adding to your my.cnf:
[mysql] local-infile=1 [mysqld] local-infile=1
Tell the connection in PHP that it may use LOCAL INFILE
mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE mysql_select_db(database);
$conn = mysqli_init(); mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true); mysqli_real_connect($conn,server,user,code,database);
Give MySQL user FILE permission
When using LOCAL this shouldn't be necessary, though. LOCAL says that the file is located on the client server (where you have PHP is installed), otherwise it looks at server location (where MySQL is installed).
GRANT FILE ON *.* TO 'mysql_user'@'localhost'
I had exactly the same problem on a EC2 Ubuntu 12.04 LTS instance when accessing a MySQL on RDS:
LOAD DATA LOCAL INFILE... works fine on a mysql console but not from PHP. Accidentaly i found out that it worked fine on another almost identical machine that used MariaDB (a binary compatible drop in replacement for MySQL).
So i replaced the MySQL clients with the ones from MariaDB and it worked.
LOAD DATA LOCAL INFILE executes regardless of the warnings. it works on mysql client since it allows the execution of queries, ignoring warnings. Though it later prints out the warnings. It refuses in PHP though because a warning will halt the script.
If you use an Ubuntu server, you can try to install
sudo apt-get install php5-mysqlnd
Easiest solution, that may work on some servers is to remove LOCAL like:
Original:LOAD DATA LOCAL INFILE New/ It should be: LOAD DATA INFILE
Strange, but I have found this solution to work on my local machine, with xampp but it did not work on a live server with CentOS, so I'd to revert the code back and add 'LOCAL'.