Bruce,
Thanks very much, I do see it in the several comments, and it appears to work
as well!
Thanks to Tim and then particularly Bruce for showing me the way, I do
appreciate it!
So in a nutshell:
When a mysql command executed in a perl DBI do or execute method:
load data local infile 'TEMP_LOAD_DATA_7318' into table ports ;
Gives the error message:
DBD::mysql::db do failed: The used command is not allowed with this
MySQL version
It is because of the special treatment of load data, explained in 6.1.6.
Security Issues with LOAD DATA LOCAL
*If you use LOAD DATA LOCAL in Perl scripts or other programs that read the
[client] group from option files, you can add the local-infile=1 option to that
group. However, to keep this from causing problems for programs that do not
understand local-infile, specify it using the loose- prefix:
[client]
loose-local-infile=1
*If LOAD DATA LOCAL is disabled, either in the server or the client, a client
that attempts to issue such a statement receives the following error message:
ERROR 1148: The used command is not allowed with this MySQL version
And the "option files" can be the dsn setup line in my perl program:
my $dsn = "DBI:mysql:database=test;host=myhost;mysql_local_infile=1;"
And then the load data local infile command will work without this error, and
load the data!
I hope this helps.
Mark Vancura
LSI Corporation
-----Original Message-----
From: Bruce Johnson [mailto:john...@pharmacy.arizona.edu]
Sent: Thursday, May 09, 2013 12:10 PM
Cc: dbi-users@perl.org (dbi-users@perl.org)
Subject: Re: (Fwd) Perl DBI question
On May 9, 2013, at 10:47 AM, "Vancura, Mark D (Mark)" <mark.vanc...@lsi.com>
wrote:
> Bruce,
> Thanks, the explanation makes sense, so I need to figure out how to
> follow through on this documented suggestion from: 6.1.6. Security
> Issues with LOAD DATA LOCAL
>
> *If you use LOAD DATA LOCAL in Perl scripts or other programs that read the
> [client] group from option files, you can add the local-infile=1 option to
> that group. However, to keep this from causing problems for programs that do
> not understand local-infile, specify it using the loose- prefix:
> [client]
> loose-local-infile=1
>
> *If LOAD DATA LOCAL is disabled, either in the server or the client, a client
> that attempts to issue such a statement receives the following error message:
> ERROR 1148: The used command is not allowed with this MySQL version
>
> Thus this fits my situation exactly, I just have to learn where the
> "option files" are and how to put the [client] loose-local-infile=1 into it.
>
> Any suggestions where to look this up?
right there in theat section you looked at, down in the comments is
"For use in perl DBI scripts, adding an option at the end of the data source
definition for DBI->connect fixes the LOAD DATA LOCAL problem in some
situations...
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);"
the $dsn creation statement is where you can put a ton of options; mysql-only
ones would be documented in the DBD:mysql documentation, I'll bet.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs
