PHP MySQL used command not allowed

Question

I'm trying to use the LOAD DATA LOCAL INFILE command in MySQL, but I keep getting an error in PHP/Joomla stating: The used command is not allowed with this MySQL version

I've spent quite a while Googling around, but the only suggestions I've seen involve adding local-infile = 1 to my my.cnf file (which I've already done, in [client], [mysql], and [mysqld]).

Additionally, if I connect from my Apache server to the MySQL server from the command line (so not using PHP), I can run LOAD DATA LOCAL without issue (so it can't be a permissions thing).

I've also checked php.ini, and sure enough, mysqli.allow_local_infile is set to 'On'.

Am I missing something here? Do you have to do something special in Joomla to make this work correctly?

How are you doing this in Joomla? What is the php code that you are using? — David Fritsch, Mar 24 '13 at 03:25
@DavidFritsch, here's what I have: $db = JFactory::getDBO(); $db->setQuery("LOAD DATA LOCAL INFILE '$path' INTO TABLE $table"); $db->query(); — Rob, Mar 24 '13 at 03:54
Which version of PHP are you using? I've seen the same problem on Ubuntu 12.10 with PHP 5.4.6-1ubuntu1.2. It seems to be a bug in PHP: https://bugs.php.net/bug.php?id=55737&edit=3. — Michael Härtl, Mar 24 '13 at 11:24
@MichaelHärtl, those are the versions I was using. I did consider it was a bug in PHP, so I upgraded to 5.4.13, but that didn't solve the problem. I'm currently switching distros to CentOS to see if that solves my problem. Starting to sound like it's an issue with both Ubuntu and PHP. — Rob, Mar 24 '13 at 14:08

score 4 · Answer 1 · edited May 23 '17 at 11:45

I just answered a similar question here, maybe it can help:

After going from MySQL 5.0 to 5.5 I found out that I suddenly have to enable LOCAL INFILE specifically when creating the connection in PHP.

Using mysql:

mysql_connect(server,user,code,false,128); // 128 enables LOCAL INFILE
mysql_select_db(database);

Using mysqli:

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

score 1 · Answer 2 · answered Apr 07 '13 at 20:34

I ran into the same problem, in my case it was the Joomla user lacking privileges that root has; giving the full privileges to the J user solved it, but then my component is for distribution so I changed to using standard .sql file and parsing / creating them manually. Hope this helps.

PHP MySQL used command not allowed

2 Answers2