"DBD::mysql::db do failed: The used command is not allowed with this MySQL version"
when using the mysql 'LOAD LOCAL FILE' command. We are not entirely sure if this is an issue caused by the mysql upgrade or an outdated perl DBD:Mysql module. MySQL server has the local_infile set to ON via a show variables command (see below) and user has the FILE permission to the local file. A reinstall of the DBD:Mysql module also did not resolve the issue.
mysql> show variables like 'local%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile | ON |
+---------------+-------+
Looking online we came through various links that suggested we needed to specify a option "mysql_local_infile" while connecting to mysql via the DBD:Mysql module. This seemed to resolve our issue.
use strict;
use DBI;
my $dsn = "DBI:mysql:mydb;mysql_local_infile=1";
my $user = "me";
my $password = "secret";
my $dbh = DBI->connect($dsn,$user,$password);
References:
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
http://www.perlmonks.org/?node_id=728472
http://www.bigresource.com/MYSQL-LOAD-DATA-INFILE-command-to-import-a-text-f
ile-rODHiNex.html
The problem that we both had here was that we couldn't set the variable in my.conf. But we can apparenly set it perfectly fine in a perl script. The purpose of adding all this granularity to the "local_infile" variable was supposedly security.
ReplyDeleteSo... just to get this straight... I can't manage this security issue as an administrator, but I can circumvent it easily by editing a perl script.
Epic fail, MySQL.
I banging my head against the wall trying to solve the exact same problem; tried your solution and it worked like a charm! Thank you!
ReplyDeleteThank a lot it worked smoothly
ReplyDelete