You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@geronimo.apache.org by Rafal Rusin <ra...@gmail.com> on 2009/09/14 10:54:24 UTC
Testing SQL connection before return in outbound connection pool
using spring
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
Re: Testing SQL connection before return in outbound connection pool
using spring
Posted by Rafal Rusin <ra...@gmail.com>.
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 <fo...@gmail.com>:
> 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
Re: Testing SQL connection before return in outbound connection pool
using spring
Posted by Forrest Xia <fo...@gmail.com>.
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
Re: Testing SQL connection before return in outbound connection pool
using spring
Posted by Rafal Rusin <ra...@gmail.com>.
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 <fo...@gmail.com>:
> 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
Re: Testing SQL connection before return in outbound connection pool
using spring
Posted by Forrest Xia <fo...@gmail.com>.
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!
Re: Testing SQL connection before return in outbound connection pool
using spring
Posted by Rafal Rusin <ra...@gmail.com>.
2009/9/14 David Jencks <da...@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
Re: Testing SQL connection before return in outbound connection pool using spring
Posted by David Jencks <da...@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.
thanks
david jencks
>
> Regards,
> --
> Rafał Rusin
> http://www.touk.pl
> http://top.touk.pl
> http://www.mimuw.edu.pl/~rrusin