Hi Jaswant- I have attached my diffs against Apache::DBI v0.88. However, I do not believe this will fix your problem. There may be 2 sources of your problem that I can think of. One is that you specified a very long ping time out with Apache::DBI::setPingTimeOut for your oracle connection. You didn't mention that you were doing this. I believe it is therefore more likely that you may be caching a database handle once the startup occurs (perhaps as a global) rather than having your scripts call connect periodically. If you are caching the handle, you subvert the ping logic as that is being done in the connect routine. You will need to ping the handle before using it.
Craig > I saw the following message on the DBI mailing list from you. > I have the same problem. I two connections to different databases - one > to oracle & other to MySQL. When the Oracle DB goes down, DBI never refreshes > the Oracle $dbh after the Oracle db come back up. I'm using persistent connection method using Apache::DBI in the startup script to open connection to the Oracle database. > > Will your fix listed in the message, address this problem. I'm > asking this question because I have MySQL & I wonder if it would affect > MySQL connection. Please help !! > > Thanks > jaswant > ========================================================================= > Hello all, > > We are using: > Apache 1.3.20 > modperl 1.26 > Apache::DBI 0.88 > DBI 1.20 > DBD::Oracle 1.12 > > We have come across an issue with Apache::DBI where it is making an incorrect assumption about the need to ping a cached connection. The code is written such that LastPingTIme and PingTimeOut hashes are keyed by DSN, not the whole connect string. > > A small subset of our web application requires transactional updates to the database. The application has been written so that most scripts optionally pass a database handle, but most don't bother. The transactional actions always pass around a database handle. However, since most scripts don't pass a database handle, there may be subs that get called that casually do a database get in the middle of a transaction. > > This has caused us problems because the database read will do another connect to the same dsn and AutoCommit will get turned on on our transactional handle (since it is the same DSN). > > In order to get around this issue without requiring database handles to be passed everywhere, we use the ability to distinguish connections based on the full set of parameter values passed to connect. We added the AutoCommit parameter to the connect string. > > Now Apache::DBI (and DBI) will differentiate between the read/only (AutoCommit = 1) handle and the transactional handle. The problem is that Apache::DBI uses only the dsn values to determine whether a ping is required or not. Since I have 2 connections to the database on the same DSN, I get errors when I try to start a transaction on a cached connection that had been timed out on the Oracle side. > > I can update our version of Apache::DBI, but I don't want to run a custom version of Apache::DBI. I hope that a maintainer for Apache::DBI would make this straightforward fix. What needs to be done is to key LastPingTime by $Idx rather than $dns. > > Thanks in advance for your help. > > Craig Leckband > TidalPool Solutions, Inc. > >
DBI.diff
Description: Binary data
