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