2009/9/14 David Jencks <david_jen...@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
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;
}
