This would defer the code in
_factory.activateObject(pair.value);
which is
conn.setAutoCommit(_defaultAutoCommit);
conn.setReadOnly(_defaultReadOnly);
until after the 'validateStatement' is executed. I'm assuming that this is
ok to do, since the validate statement should only be something simple like
a 'select for a given row'.
I'll use the code you sent, will you be putting this back in CVS?
Paul Extance
-----Original Message-----
From: Kyrill Alyoshin [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 30, 2003 8:38 AM
To: [EMAIL PROTECTED]
Subject: Re: [pool] Possible Bug (and fix) with testOnBorrow...
Good catch, I think. But wrapping everything in try/catch block may be an
overkill here.
********************************************************
_factory.activateObject(pair.value);
if(_testOnBorrow && !_factory.validateObject(pair.value)) {
_factory.destroyObject(pair.value);
if(newlyCreated) {
throw new NoSuchElementException("Could not create a
validated object");
} // else keep looping
} else {
_numActive++;
return pair.value;
}
*********************************************************
What I'd rather do is this:
if(_testOnBorrow && !_factory.validateObject(pair.value)) {
try {
//will call close() method on connection and still throw
exception
_factory.destoryObject(pair.value)
}
catch(Exeption e) {
;
}
if(newlyCreated) {
throw new NoSuchElementException("Could not create a validated
object");
} // else keep looping
} else {
_factory.activateObject(pair.value);
_numActive++;
return pair.value;
}
What do you think?
Kyrill Alyoshin
> Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
> List-Unsubscribe: <mailto:[EMAIL PROTECTED]>
> List-Subscribe: <mailto:[EMAIL PROTECTED]>
> List-Help: <mailto:[EMAIL PROTECTED]>
> List-Post: <mailto:[EMAIL PROTECTED]>
> List-Id: "Jakarta Commons Users List" <commons-user.jakarta.apache.org>
> Delivered-To: mailing list [EMAIL PROTECTED]
> From: "Extance, Paul" <[EMAIL PROTECTED]>
> To: "'Jakarta Commons Users List'" <[EMAIL PROTECTED]>
> Subject: [pool] Possible Bug (and fix) with testOnBorrow...
> Date: Tue, 29 Jul 2003 17:39:08 -0700
> X-Server: High Performance Mail Server - http://surgemail.com
> X-Spam-Rating: daedalus.apache.org 1.6.2 0/1000/N
>
> Hello,
>
> I'm using the latest CVS build of commons-pool and commons-dbcp with
Tomcat
> 4.1.24 and used the connection pool as a DataSource for container
> authentication.
>
> With this current build, if all the connections in the pool are
> 'invalidated' (ie the DBA kills them or restarts the database) I want
> connection pool to makes sure the connection is 'valid' before returning
it.
> In theory this is what the 'testOnBorrow' should do for me.
>
> I'm using Oracle 9i, I have the following in tomcat/server.xml
>
> <Resource
> name = "dbcp/aura4d"
> auth = "Container"
> type = "javax.sql.DataSource"
> />
> <ResourceParams name = "dbcp/aura4d">
> <parameter>
> <name>debug</name>
> <value>1</value>
> </parameter>
> <parameter>
> <name>factory</name>
> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
> </parameter>
> <parameter>
> <name>username</name>
> <value>xxxx</value>
> </parameter>
> <parameter>
> <name>password</name>
> <value>xxxx</value>
> </parameter>
> <parameter>
> <name>driverClassName</name>
> <value>oracle.jdbc.driver.OracleDriver</value>
> </parameter>
> <parameter>
> <name>url</name>
> <value>jdbc:oracle:thin:@xxx:1521:xxx</value>
> </parameter>
> <parameter>
> <name>maxActive</name>
> <value>5</value>
> </parameter>
> <parameter>
> <name>maxIdle</name>
> <value>2</value>
> </parameter>
> <parameter>
> <name>maxWait</name>
> <value>100</value>
> </parameter>
> <parameter>
> <name>testOnBorrow</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>testOnReturn</name>
> <value>true</value>
> </parameter>
> <parameter>
> <name>validationQuery</name>
> <value>select 1 from dual</value>
> </parameter>
> </ResourceParams>
>
>
> The problem is that in
> pool\src\java\org\apache\commons\pool\impl\GenericObjectPool.java an
> SQLException is thrown before the testOnBorrow is reached (see below)
>
> java.sql.SQLException: ORA-00028: your session has been killed
>
> at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
> at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
> at oracle.jdbc.ttc7.Ocommoncall.receive(Ocommoncall.java:140)
> at
oracle.jdbc.ttc7.TTC7Protocol.setAutoCommit(TTC7Protocol.java:441)
> at
>
oracle.jdbc.driver.OracleConnection.setAutoCommit(OracleConnection.java:996)
> at
>
org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnect
> ion.java:243)
> at
>
org.apache.commons.dbcp.PoolableConnectionFactory.activateObject(PoolableCon
> nectionFactory.java:271)
> at
>
org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPoo
> l.java:757)
> at
>
org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.ja
> va:108)
> at
>
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:4
> 03)
>
> It looks like the setAutoCommit() called via the
> GenericObjectPool.borrowObject() causes a 'ping' to the database before it
> even gets to the _factory.validateObject() method which does the test for
> the database connection.
>
> I have made a local patch to GenericObjectPool.borrowObject() method
> changing the following code...
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> _factory.activateObject(pair.value);
> if(_testOnBorrow && !_factory.validateObject(pair.value)) {
> _factory.destroyObject(pair.value);
> if(newlyCreated) {
> throw new NoSuchElementException("Could not create a
> validated object");
> } // else keep looping
> } else {
> _numActive++;
> return pair.value;
> }
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> to the following
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> try {
> _factory.activateObject(pair.value);
> if(_testOnBorrow && !_factory.validateObject(pair.value))
{
> // Kill off this object and keep looping
> try {
> _factory.destroyObject(pair.value);
> } catch (Exception ex) {
> // ignore
> }
> if(newlyCreated) {
> throw new NoSuchElementException("Could not create
a
> validated object");
> } // else keep looping
> } else {
> _numActive++;
> return pair.value;
> }
> } catch(java.sql.SQLException e) {
> // thrown if there is an error in activateObject
> // Kill off this object and keep looping
> try {
> _factory.destroyObject(pair.value);
> } catch (Exception ex) {
> // ignore
> }
> }
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
> This way the SQLException is caught and the bad connection is removed from
> the pool, and a valid connection is still returned. Testing this under
> tomcat I can restart my database while tomcat is running and the container
> authentication works fine.
>
> If this patch makes sense to do, can someone apply this to the CVS
> repository, or is there a better patch you can apply to fix this.
>
> Thanks
>
> Paul Extance
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
