Bug #55244 for DBD-InterBase: when a txn fails, should clear sth cache

Thu Mar 04 22:35:08 2010 sam [...] nipl.net - Ticket created

Subject:	when a txn fails, should clear sth cache
Date:	Fri, 5 Mar 2010 03:34:34 +0000
To:	bug-DBD-InterBase [...] rt.cpan.org
From:	Sam Watkins <sam [...] nipl.net>

We are using DBIx::Class with DBD::InterBase and Firebird. DBIx::Class uses $dbh->prepare_cached(...) to cache statement handles for efficiency. The problem is that if a transaction fails and is rolled back, Firebird discards all active statements, but the cached statement handles remain. So after a failed transaction, other things fail too, they are trying to use cached handles that are no longer valid. This does not seem to occur with normal database errors, or after a successful transaction. I was able to hack around this by clearing the statement handle cache, which is described in the DBI perldoc. If a transaction fails, I call a function which includes this code: my $CachedKids = $dbh->{CachedKids}; # warn "clearing statement cache: $_\n" for keys %$CachedKids; %$CachedKids = () if $CachedKids; Since this appears to be a Firebird-specific quirk, I think it's reasonable to ask DBD::InterBase to clear the statement handle cache automatically when rollback is called. I haven't checked whether the active statements are lost when a exception occurs in a transaction, or when ROLLBACK is called; but I think it's probably a side-effect of ROLLBACK. I hope I explained this clearly, it's a bit complicated I guess! thanks, Sam Watkins

Fri Mar 19 10:05:59 2010 mjp [...] cpan.org - Correspondence added

On Thu Mar 04 22:35:08 2010, sam@nipl.net wrote: Show quoted text

> We are using DBIx::Class with DBD::InterBase and Firebird. > DBIx::Class uses > $dbh->prepare_cached(...) to cache statement handles for efficiency. > > The problem is that if a transaction fails and is rolled back, > Firebird > discards all active statements, but the cached statement handles > remain. > > So after a failed transaction, other things fail too [[SNIP]]

Please provide a simple test case to reproduce, preferably one which only uses the DBI (and not DBIx::Class). I'm unable to induce failure on DBD-IB-0.48, perl 5.10.1, FB-2.1.3 with the attached script, which does essentially this: $dbh->prepare_cached(INSERT)->execute; $dbh->rollback $dbh->prepare_cached(INSERT)->execute; ... and it isn't clear to me precisely how transactions are failing for you nor precisely what subsequent failures you are encountering.

Subject:

rt55244.t

#!/usr/bin/perl -w # # Usage: # perl rt55244.pl ib_database ib_username ib_pass [ DbiAttr=Value ] # # E.g., # $ perl rt55244.pl employee SYSDBA masterkey AutoCommit=0 # use strict; use DBI; use constant TBL_CREATE => 'create table RT55244 (I integer NOT NULL)'; use constant TBL_DROP => 'drop table RT55244'; use constant TBL_INSERT => 'insert into RT55244 (I) values (0)'; my ($dsn, $user, $pass) = splice(@ARGV, 0, 3); $dsn = "dbi:InterBase:dbname=$dsn" unless $dsn =~ /^dbi:InterBase:/; my %conn_attr = ((map { split /=/, $_, 2 } @ARGV), RaiseError => 1); my ($dbh, $sth); $dbh = DBI->connect($dsn, $user, $pass, \%conn_attr); print "AutoCommit is ", ($dbh->{AutoCommit} ? 'on' : 'off'), "\n"; $dbh->do(TBL_CREATE); $dbh->commit; $sth = $dbh->prepare_cached(TBL_INSERT); $sth->execute; $dbh->rollback; $sth = $dbh->prepare_cached(TBL_INSERT); $sth->execute; $dbh->do(TBL_DROP); $dbh->commit; $dbh->disconnect;

Fri Mar 19 10:07:03 2010 The RT System itself - Status changed from 'new' to 'open'

Thu Sep 22 10:59:21 2011 dam [...] cpan.org - Correspondence added

На 19 март 2010, пт 16:05:59, MJP написа: Show quoted text

> On Thu Mar 04 22:35:08 2010, sam@nipl.net wrote:

> > We are using DBIx::Class with DBD::InterBase and Firebird. > > DBIx::Class uses > > $dbh->prepare_cached(...) to cache statement handles for efficiency. > > > > The problem is that if a transaction fails and is rolled back, > > Firebird > > discards all active statements, but the cached statement handles > > remain.

I think this is not true. Statements should be left intact on Firebird side after the transaction is gone. Show quoted text

> > So after a failed transaction, other things fail too [[SNIP]]

> > Please provide a simple test case to reproduce, preferably one which > only uses the DBI (and not DBIx::Class).

I adapted the provided test file to DBD::Firebird[1] and it passes. I don't know why, though :) It may be the Firebird version (2.5.1 RC1) or the changes in DBD::Firebird. [1] https://github.com/mariuz/perl-dbd-firebird I hope this helps someone.

Thu Sep 22 11:00:31 2011 dam [...] cpan.org - Cc DAM added