On Mon, Jan 24, 2005 at 08:13:37AM -0000, Mark Edwards wrote:
> I have been experiencing some problems with the following configuration:
>
> Solaris 5.7 on a Sun Ultra-250, running Apache 2.0.48, mod_perl 1.9913
> (built as DSO)
>
> I'm using the following perl modules:
>
> Perl 5.8.0
> DBI 1.46
> DBD::ODBC 1.13
> Apache::DBI 0.94
>
> The RDBMS is Mimer 8.2.5G (www.mimer.com), connecting via unixODBC 2.2.0.
>
> I get an unexpected transaction commit in the following example script:
>
> $dbh = DBI->connect('dbi:ODBC:mydb','myuser','mypass', { AutoCommit => 0 })
> or die $DBI::errstr;
> $sth=$dbh->prepare("insert into mytable (mycol) select max(mycol)+1 from
> mytable") or die $dbh->errstr;
> $sth->execute or die $sth->errstr;
> # the insert statement above is (erroneously?) committed as soon as the
> following connection initiates:
> $dbh = DBI->connect('dbi:ODBC:mydb','myuser','mypass', { AutoCommit => 0 })
> or die $DBI::errstr;
> $sth=$dbh->prepare("insert into mytable (mycol) select max(mycol)+1 from
> mytable") or die $dbh->errstr;
> $sth->execute or die $sth->errstr;
> $dbh->rollback or die $dbh->errstr;
>
> My expectation for the second connect statement would be that Apache::DBI
> would simply return a handle to the already open connection from the first
> connect. Since AutoCommit is off, the final rollback would prevent both
> inserts. This was how things used to work before a substantial upgrade from
> a very old version of Apache (running Mod Perl "1").
>
> Not knowing which of the modules things were going wrong in, I hacked all of
> them, eventually using DBI tracing and some of my own diagnostic prints and
> sleeps in DBI.pm to determine the problem location. It appears to take
> place on setting $dbh->{AutoCommit} in the connect subroutine of DBI.pm.
>
> So, I've patched the DBI module with the following 'fix' in "sub connect"
> ("+" lines added):
>
> +#################################################
> if (%$attr) {
>
> DBI::_rebless_dbtype_subclass($dbh, $rebless_class||$class, delete
> $attr->{DbTypeSubclass}, $attr)
> if $attr->{DbTypeSubclass};
>
> + if (exists $attr->{AutoCommit}) {
> + if ((!exists $dbh->{AutoCommit}) || ($dbh->{AutoCommit} !=
> $attr->{AutoCommit})) {
> + $dbh->{AutoCommit} = delete $attr->{AutoCommit};
> + } else {
> + delete $attr->{AutoCommit};
> + }
> + }
>
> my $a;
> foreach $a (qw(RaiseError PrintError AutoCommit)) { # do these first
> next unless exists $attr->{$a};
> $dbh->{$a} = delete $attr->{$a};
> }
> foreach $a (keys %$attr) {
> eval { $dbh->{$a} = $attr->{$a} } or $@ && warn $@;
> }
> }
> +#################################################
>
> Now $dbh->{AutoCommit} only gets set if it doesn't already exist or its
> value (copied from $attr) has changed.
>
> This patch immediately fixed the example script and appears to have fixed
> the problem in general. I'm not 100% convinced there have been no side
> effects.
>
> Has anyone else experienced this kind of problem? Is the above patch a
> reasonable thing to do?
Sadly not. The way DBI->connect applies attributes should not be a
special case.
DBD::ODBC shouldn't perform a commit due to $dbh->{AutoCommit} = 0
and if you look at the code you'll see that it doesn't. It just calls
the ODBC SQLSetConnectOption() function.
So it's probable that the bug is in your Mimer ODBC driver.
A patch to DBD::ODBC could be used to work around it.
Tim.
