| Subject: | DBD-mysql autocommit bug with possible fix |
First the basics:
$ perl -MDBD::mysql -e 'print $DBD::mysql::VERSION . "\n"'
4.022
$ perl -MDBI -e 'print $DBI::VERSION . "\n"'
1.616
It appears that DBI “AutoCommit” is not working as expected when
autocommit is set to off in the database. I have written a script
(attached) that demonstrates the issue. Below I have two runs of the
script. The first one is with DBD-mysql-4.022 unmodified. You can see
that scenarios B and C exhibit the issue at hand. In the second run, I
commented out lines 2316 thru 2319 in dbdimp.c and recompiled prior to
running. All scenarios ran as expected in this run.
RUN 1
########## scenario A ##########
* autocommit off per database and AutoCommit set to 0 at connect
mysql autocommit = OFF
perl AutoCommit = ''
* enable autocommit via DBI on the same connection
mysql autocommit = ON
perl AutoCommit = '1'
########## scenario B ##########
* autocommit off per database and AutoCommit not set at connect
mysql autocommit = OFF
perl AutoCommit = '1'
* enable autocommit via dbi on the same connection
mysql autocommit = OFF
perl AutoCommit = '1'
* disable autocommit via DBI on the same connection
mysql autocommit = OFF
perl AutoCommit = ''
########## scenario C ##########
* autocommit off per database and AutoCommit set to 1 at connect
mysql autocommit = OFF
perl AutoCommit = '1'
* enable autocommit via dbi on the same connection
mysql autocommit = OFF
perl AutoCommit = '1'
* disable autocommit via DBI on the same connection
mysql autocommit = OFF
perl AutoCommit = ''
RUN 2
########## scenario A ##########
* autocommit off per database and AutoCommit set to 0 at connect
mysql autocommit = OFF
perl AutoCommit = ''
* enable autocommit via DBI on the same connection
mysql autocommit = ON
perl AutoCommit = '1'
########## scenario B ##########
* autocommit off per database and AutoCommit not set at connect
mysql autocommit = ON
perl AutoCommit = '1'
* enable autocommit via dbi on the same connection
mysql autocommit = ON
perl AutoCommit = '1'
* disable autocommit via DBI on the same connection
mysql autocommit = OFF
perl AutoCommit = ''
########## scenario C ##########
* autocommit off per database and AutoCommit set to 1 at connect
mysql autocommit = ON
perl AutoCommit = '1'
* enable autocommit via dbi on the same connection
mysql autocommit = ON
perl AutoCommit = '1'
* disable autocommit via DBI on the same connection
mysql autocommit = OFF
perl AutoCommit = ''
| Subject: | example.pl |
#!/usr/bin/perl
###### PACKAGES ######
use strict;
use warnings;
use Getopt::Long;
Getopt::Long::Configure('no_ignore_case');
use DBI;
###### CONSTANTS ######
###### GLOBAL VARIABLES ######
use vars qw($Host $DbName $Dbh);
###### MAIN PROGRAM ######
parse_cmd_line();
my $dsn = "DBI:mysql:host=$Host;database=$DbName";
my $user = 'ops';
my $pass = 'ops';
print "#" x 10, " scenario A ", '#' x 10, "\n\n";
$Dbh = DBI->connect(
$dsn, $user, $pass,
{ RaiseError => 1,
PrintError => 0,
AutoCommit => 0,
}
);
print "* autocommit off per database and AutoCommit set to 0 at connect\n";
show_vars();
print "* enable autocommit via DBI on the same connection\n";
$Dbh->{AutoCommit} = 1;
show_vars();
print "#" x 10, " scenario B ", '#' x 10, "\n\n";
$Dbh = DBI->connect(
$dsn, $user, $pass,
{ RaiseError => 1,
PrintError => 0
}
);
print "* autocommit off per database and AutoCommit not set at connect\n";
show_vars();
print "* enable autocommit via dbi on the same connection\n";
$Dbh->{AutoCommit} = 1;
show_vars();
print "* disable autocommit via DBI on the same connection\n";
$Dbh->{AutoCommit} = 0;
show_vars();
print "#" x 10, " scenario C ", '#' x 10, "\n\n";
$Dbh = DBI->connect(
$dsn, $user, $pass,
{ RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
}
);
print "* autocommit off per database and AutoCommit set to 1 at connect\n";
show_vars();
print "* enable autocommit via dbi on the same connection\n";
$Dbh->{AutoCommit} = 1;
show_vars();
print "* disable autocommit via DBI on the same connection\n";
$Dbh->{AutoCommit} = 0;
show_vars();
###### END MAIN #######
sub show_vars {
my $sql = q{show variables like 'autocommit'};
my $ac = $Dbh->selectrow_arrayref($sql)->[1];
print "\tmysql autocommit = $ac\n";
print "\tperl AutoCommit = '" . $Dbh->{AutoCommit} . "'\n\n";
}
sub check_required {
my $opt = shift;
my $arg = shift;
print_usage("missing arg $opt") if !$arg;
}
sub parse_cmd_line {
my @tmp = @ARGV;
my $help;
my $rc = GetOptions(
"h=s" => \$Host,
"d=s" => \$DbName,
"help|?" => \$help
);
print_usage("usage:") if $help;
check_required( '-h', $Host );
check_required( '-d', $DbName );
if ( !($rc) || ( @ARGV != 0 ) ) {
## if rc is false or args are left on line
print_usage("parse_cmd_line failed");
}
@ARGV = @tmp;
}
sub print_usage {
print STDERR "@_\n";
print "\n$0\n"
. "\t-h <host>\n"
. "\t-d <dbname>\n"
. "\t[-?] (usage)\n" . "\n";
exit 1;
}