web analytics

The used command is not allowed with this MySQL version – LOAD DATA LOCAL INFILE

This error normally occurs when you run the ‘load data local infile’ command in mysql.

If you are seeing this error in shell, please check this link

If you are seeing this error while running a php script, here are the following things you can test.

I have a php script as follows for testing.


<?php
mysql_connect(“localhost”,”myuser”,”mypass”)or die(mysql_error());
mysql_select_db(“abc_test”) or die(mysql_error());

$query =”LOAD DATA LOCAL INFILE ‘/home/abc/public_html/test.csv’ INTO TABLE abctesting”;
mysql_query($query) or die(mysql_error());

?>

The file ‘test.csv’ contains some random input and if when this script runs, my database abc_test should populate the table abctesting with contents from the file ‘test.csv’

Unfotunately, when I ran this script over browser, I got the following error message

The used command is not allowed with this MySQL version

Following are the things that I checked.


1) Opened my.cnf and added the line “local-infile=1”  under [mysqld] and [mysql]

[mysqld]
local-infile=1

[mysql]
local-infile=1

Save and quit the file.

/etc/init.d/mysql restart

2) The above should allow you to run the command in mysql shell, but you are having problems while running it over browser as a php script.
So check if it is enabled in php.

root@abc#grep mysql.allow_local_infile /etc/php5/apache2/php.ini
mysql.allow_local_infile = On

Now, the above command shows it is enabled. If it is Off, change it to On and restart apache.

3) I tried modifying my script a bit by editing the connect command as follows.

mysql_connect(“localhost”,”myuser”,”mypass”,”false”,128)

Hurray, my script worked like a charm and I saw that my database is populated as mentioned in the script.

If interested, 128 in the above line is to enable LOAD DATA LOCAL handling

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>