Testing SQL connection before return in outbound connection pool using spring

[Rafal Rusin]

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.

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

[Forrest_Xia]

In geronimo, there is GBean "org.apache.geronimo.connector.DatabaseInitializationGBean", which has an attribute to test a connection before executing the target sql.

 

A sample about how to use it, refer to http://cwiki.apache.org/GMOxDOC22/configuring-your-own-monitoring-plugin-datasource.html

 

Hope this helpful! Good luck!

 

[Rafal Rusin]

Well, this testSQL property is used for database initialization (it
determines whether to execute db initialization scripts).
But my problem is that after losing SQL connection to database during
server uptime, closed connections still remain in Managed Connection
Pool (SinglePool). So a solution for it is to test SQL connection
before borrow from pool.
But I'm not able to configure it in Geronimo for Managed Connections.

2009/9/14 Forrest Xia <forrestxm@...>:
> In geronimo, there is GBean
> "org.apache.geronimo.connector.DatabaseInitializationGBean", which has an
> attribute to test a connection before executing the target sql.
>
> A sample about how to use it, refer to
> http://cwiki.apache.org/GMOxDOC22/configuring-your-own-monitoring-plugin-datasource.html
>
> Hope this helpful! Good luck!
>


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

[Forrest_Xia]

There is some known issue on geronimo db connectivity. Not sure if this JIRA discussion is helpful for your problem.

 

https://issues.apache.org/jira/browse/GERONIMO-4222

[Rafal Rusin]

Yes, this is very helpful, thanks a lot!
My configuration is very similar, since I also use tranql vendor
(oracle) package.

2009/9/14 Forrest Xia <forrestxm@...>:
> There is some known issue on geronimo db connectivity. Not sure if this JIRA
> discussion is helpful for your problem.
>
> https://issues.apache.org/jira/browse/GERONIMO-4222


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

[djencks]

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.

thanks
david jencks

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

[Rafal Rusin]

2009/9/14 David Jencks <david_jencks@...>:

>
> 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

[tranql-vendor-oracle-closed-connections.patch]

---

Index: tranql-connector-oracle-common/src/main/java/org/tranql/connector/oracle/OracleExceptionSorter.java
===================================================================
--- tranql-connector-oracle-common/src/main/java/org/tranql/connector/oracle/OracleExceptionSorter.java (revision 651)
+++ tranql-connector-oracle-common/src/main/java/org/tranql/connector/oracle/OracleExceptionSorter.java (working copy)
@@ -37,7 +37,7 @@
         if (e instanceof SQLException) {
             SQLException sqlException = (SQLException) e;
             int errorCode = sqlException.getErrorCode();
-            return errorCode == 600;
+            return errorCode == 600 || errorCode == 17008;
         }
         return false;
     }

---