Skip Menu |
 

This queue is for tickets about the DBD-InterBase CPAN distribution.

Report information
The Basics
Id: 55244
Status: open
Priority: 0/
Queue: DBD-InterBase

People
Owner: Nobody in particular
Requestors: sam [...] nipl.net
Cc: dam [...] cpan.org
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



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>
Download (untitled) / with headers
text/plain 1.2k
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
Download (untitled) / with headers
text/plain 897b
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
Download rt55244.t
text/x-perl 929b
#!/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;
Download (untitled) / with headers
text/plain 973b
На 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.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.