"Load data local infile" command not allowed

Question

I am using the PHP mysqli library. Every time I try to run a LOAD DATA LOCAL INFILE command, mysqli complains with the message

The used command is not allowed with this MySQL version

I do not have the same problem with running the command from a MySQL terminal (must login with --local-infile=1 to make it work) or PHPMyAdmin. Just my PHP+mysqli code experiences this error.

I tried setting this option:

mysqli_options($cnx, MYSQLI_OPT_LOCAL_INFILE, 1);

prior to my load data call, but still no-effect.

How do I correct this problem?

Answer 1

There are a couple things that could be wrong. Try the following:

Add to your my.cnf:

[mysql]
local-infile=1

[mysqld]
local-infile=1

Enable local infile when connecting from PHP

$conn = mysqli_init();
mysqli_options($conn, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($conn,server,user,code,database);

Answer 2

in addition to mentioned "local-infile=1" in mysql configuration files (/etc/mysql/my.cnf), you may need

[MySQLi]
mysqli.allow_local_infile = On

[MySQL]
mysql.allow_local_infile = On

in your system php.ini (/etc/php5/cgi/php.ini or similar - local php.ini won't work)

And another big thing - if your using "open_basedir" in your php.ini (no matter what values are in), the functionality will be silently disabled! So you have to give up on open_basedir if you're using "load data local infile" (at least for PHP 5.4.4, as found in Debian Wheezy)