Error 1148 MySQL The used command is not allowed with this MySQL version
Solution 1:
Loading a local file in MySQL is a security hazard and is off by default, you want to leave it off if you can. When it is not permitted you get this error:
ERROR 1148 (42000): The used command is not allowed with this MySQL version
Solutions:
-
Use
--local-infile=1
argument on the mysql commandline:When you start MySQL on the terminal, include
--local-infile=1
argument, Something like this:mysql --local-infile=1 -uroot -p mysql>LOAD DATA LOCAL INFILE '/tmp/foo.txt' INTO TABLE foo COLUMNS TERMINATED BY '\t';
Then the command is permitted:
Query OK, 3 rows affected (0.00 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
-
Or send the parameter into the mysql daemon:
mysqld --local-infile=1
-
Or set it in the my.cnf file (This is a security risk):
Find your mysql
my.cnf
file and edit it as root.Add the
local-infile
line under the mysqld and mysql designators:[mysqld] local-infile [mysql] local-infile
Save the file, restart mysql. Try it again.
More info can be found here: http://dev.mysql.com/doc/refman/5.1/en/load-data-local.html
Solution 2:
In addition to using local-infile
with the MySQL server (you can put this in the /etc/my.cnf file too), you also need to enable PDO to allow it:
<?php
$pdo = new PDO($dsn, $user, $password,
array(PDO::MYSQL_ATTR_LOCAL_INFILE => true)
);
Otherwise it won't work, regardless of the value of local-infile
on the MySQL server.