Re: DBI AutoCommit goes away when db connection is killed

[Robert Landrum <suppressed>]

Lev Lvovsky wrote:
> > > In testing out persistent connections while using transactions, we've 
> > > noticed that while in a loop which continuously begins and ends a 
> > > transaction, killing the persistent connection which Apache::DBI is 
> > > maintaining causes the still-running handler to report things like:
> > > error: DBD driver has not implemented the AutoCommit attribute at 
> > > /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/DBI.pm line 
> > > 1668.
> 1   -> STORE for DBD::mysql::db (DBI::db=HASH(0x9bf49cc)~INNER 
> 'AutoCommit' 0) thr#915dc30
>     --> do_error
> Turning off AutoCommit failed error 21 recorded: Turning off AutoCommit 
> failed
>     <-- do_error
>     STORE DBI::db=HASH(0x9bf49cc) 'AutoCommit' => 0
>     !! ERROR: 21 CLEARED by call to begin_work method
>     -> begin_work for DBD::mysql::db (DBI::db=HASH(0x9b8678c)~0x9bf49cc) 
> thr#915dc30

So, your code is calling begin_work, fetching a value, storing a value, 
and committing?  The store will fail if the database connection goes 
away, and it'll do so when it attempts communicate with the server to 
set AutoCommit to 0.

You might want to call $dbh->ping before calling your store routine. 
Either way, the way to make this fault tolerant is with eval.

Once you've determined that there's been a fault, and that the fault 
means that the database is down, try to reconnect.  Continue to try to 
reconnect until the database comes back online.  Then retry the original 
failed transaction.

Once you reconnect, the connection is persistent again.  At least, 
that's been my experience with postgresql.

Rob