[cgiapp] cached DBI connections with cgiapp/fastcgi

[Benjamin Hitz]

On Oct 22, 2008, at 10:48 AM, Perrin Harkins wrote:

> On Wed, Oct 22, 2008 at 1:33 PM, Benjamin Hitz <hitz at genome.stanford.edu 
> > wrote:
>> It basically calls DBI->connect_cached("dbi:Oracle:sdev2", $user,  
>> $pass, {
>> RaiseError=>1, AutoCommit=>0, private_cachekey => $0 });
>
> That ought to work.  The connect_cached call should ping the
> connection and reconnect if it's stale.  You might want to turn on
> DBI_TRACE to figure out why it isn't doing that.  This used to be
> referred to as "the morning bug" on the mod_perl list and usually
> happened with DBD drivers that lie about ping (return true when the
> handle is not really connected).

Good advice.

>
>
> Incidentally, I hope you have something in there to catch errors at
> the top level of your FastCGI adapter and issue a rollback on any open
> database handles.  If you don't, you might leave some partially
> completed uncommitted data around that the next request would commit.
> Aoache::DBI solves this for mod_perl by doing a rollback on all open
> connections after every request, in case the request exited
> abnormally.


Thanks for the tip.  At this juncture we are only planning to convert  
the "read only"
interfaces to FCGI.  In our system we have maybe 10 users who are  
allowed to commit, and 1,000s that just read.

On Oct 22, 2008, at 10:39 AM, Michael Peters wrote:
>
> It will probably reconnect if the handle isn't there. But not if it  
> goes bad. I don't know for sure since I don't use that plugin. But  
> it's a common problem when dealing with persistant DB connections.  
> If you want to see how mod_perl folks handle, take a look at  
> Apache::DBI. Look at it's connect() method and particularly how it  
> uses ping().


Thanks... I will check it out.

--
Ben Hitz
Senior Scientific Programmer ** Saccharomyces Genome Database ** GO  
Consortium
Stanford University ** hitz at genome.stanford.edu