load local infile not allowed perl mysql

Question

Who knows how to turn on load local infile settings in the latest MySQL on Ubuntu? I edited the /etc/mysql/my.cnf file and added

local-infile=1

And in the Perl code

dbConnectString=dbi:mysql:orthomcl:mysqllocalinfile=1

but still get

DBD::mysql::st execute failed: The used command is not allowed with this MySQL version...

It is used by an application called OrthoMCL

I tried configuring my.cnf file:

sudo vim /etc/mysql/my.cnf

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
local-infile = 1
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
local-infile = 1

Then a restart of the mysql server:

sudo service mysql restart
mysql stop/waiting
mysql start/running, process 9563

But then I still get:

DBD::mysql::st execute failed: The used command is not allowed with this MySQL version at ../Apps/orthomclSoftware-v2.0.3/bin/orthomclLoadBlast line 39, <F> line 12.
The used command is not allowed with this MySQL version at ../Apps/orthomclSoftware-v2.0.3/bin/orthomclLoadBlast line 39, <F> line 12.`

Line 39 is:

$stmt->execute() or die DBI::errstr;

which executes the lines above:

my $sql = "
LOAD DATA
LOCAL INFILE \"$blastFile\"
REPLACE INTO TABLE $sst
FIELDS TERMINATED BY '\\t'
";
my $stmt = $dbh->prepare($sql) or die DBI::errstr;

[Set Load Infile](http://stackoverflow.com/questions/10762239/enable-load-data-local-infile) Update the facing issues @Jasper — thar45, Oct 31 '12 at 09:53
I updated my post above, and mentioned that I indeed tried that post in several ways but none seems to work. — Jasper, Oct 31 '12 at 10:04
your `dbConnectString` has a weird format for me. It should be like `dbi:DriverName:database_name` — w.k, Oct 31 '12 at 10:56
Changed it to dbConnectString=dbi:mysql:orthomcl but no success. — Jasper, Oct 31 '12 at 11:30

score 7 · Answer 1 · edited Dec 27 '12 at 22:39

7

dbConnectString=dbi:mysql:orthomcl:mysql_local_infile=1:localhost:3306

I added also in mysql.cnf under

[client]
      loose-local-infile=1

worked for me

edited Dec 27 '12 at 22:39

Anders R. Bystrup

15,729
10
59
55

answered Dec 27 '12 at 22:21

baverhey

71
2

Solved my problem with orthoMCL too. Thanks. – Morlock Sep 04 '14 at 13:32
Adding mysql_local_infile=1 to the DBI DSN should be sufficient. – Sasha Pachev Oct 12 '17 at 02:42

load local infile not allowed perl mysql

1 Answers1