geronimo-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rafal Rusin <rafal.ru...@gmail.com>
Subject Re: Testing SQL connection before return in outbound connection pool using spring
Date Tue, 15 Sep 2009 07:33:11 GMT
2009/9/14 David Jencks <david_jencks@yahoo.com>:
>
> On Sep 14, 2009, at 1:54 AM, Rafal Rusin wrote:
>
>> Hello,
>>
>> I tried to configure a testing SQL connection factory in ServiceMix,
>> but I haven't found any signs of it's existence. ServiceMix is based
>> on Geronimo, so I thought it's a good place to ask.
>> I need it, because after database restart Geronimo's
>> org
>> .apache.geronimo.connector.outbound.connectionmanagerconfig.SinglePool
>> returns closed connection each time getConnection is executed.
>>
>> Is there a way to configure in Spring something like
>> TestablePoolFactoryBean instead of SinglePool, with testSql attribute,
>> which would inject SQLTestingInterceptor for testing sql connection
>> before return? This would solve my problem.
>> BTW. Most application servers have a possibility to configure test sql
>> in connection pool, so there must be something in Geronimo too.
>
> I don't remember too clearly but I don't think I implemented support for
> this.  There may be an optional j2ca 1.5 feature to support this style of
> use, and it wouldn't be hard to implement, and contributions are always
> welcome.
>
> My thinking on the subject was that connections rarely break, but can break
> at any time -- for instance in the middle of a transaction.  Therefore your
> app has to be able to deal with this kind of failure anyway.  So to me it
> seems that the "test before letting out of the pool" style is going to slow
> down your app for every normal request while not relaxing the error recovery
> features you will need.  I also thought that since the db restart is
> presumably done under an admins' control, the admin could also flush the db
> pool.  I don't recall if we have a command to do this, but it seems like a
> less invasive technique than testing each connection for each use.
>
> The important feature to me is that once you discover that a managed
> connection is broken, it be removed from the pool.  There's a mechanism
> using the ConnectionEventListener to deal with this, but it relies on the
> tranql wrapper being able to decide accurately whether an exception means
> the connection is no longer usable.  The vendor specific wrappers generally
> use the similar XADataSource feature to determine this, but the generic
> wrapper has no way to decide, so by default it assumes all exceptions are
> fatal errors.

Actually I'm interested only in the "important feature".
Also, I'm using tranql (vendor oracle) and given your suggestions I
fixed this problem! Thanks a lot !!!
I had a following exception, which was ocurring endlessly after DB restart:

Caused by: java.sql.SQLException: Connection Closed
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
        at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:840)
        at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:759)
        at oracle.jdbc.OracleConnectionWrapper.prepareStatement(OracleConnectionWrapper.java:92)
        at org.tranql.connector.jdbc.ConnectionHandle.prepareStatement(ConnectionHandle.java:231)
        at org.apache.ode.scheduler.simple.JdbcDelegate.dequeueImmediate(JdbcDelegate.java:172)
        ... 8 more

There was a problem in TranQL vendor Oracle in OracleExceptionSorter class.
I modified:

    public boolean isExceptionFatal(Exception e) {
        if (e instanceof SQLException) {
            SQLException sqlException = (SQLException) e;
            int errorCode = sqlException.getErrorCode();
            return errorCode == 600;
        }
        return false;
    }

so it could recognize closed connections (Oracle code 17008) as fatal.

BTW. Surprisingly, I saw also that using LocalMCF from TranQL vendor
Oracle instead of XAMCF resolved this problem too. But in logs there
was only a single "Broken pipe" SQLException after restart and no
"Connection Closed exception".

I'll post this info on some tranql bugs page.

Regards,
-- 
Rafał Rusin
http://www.touk.pl
http://top.touk.pl
http://www.mimuw.edu.pl/~rrusin

Mime
View raw message