hi Kevin, hi Kevin, what you seem to be missing in your config is
testOnBorrow="true" Filip On 12/02/2009 05:11 AM, Kevin Jackson wrote:
Hi, We have a situation with a clustered SQL Server database and tomcat-jdbc. When the cluster fails over and the active node switches to the passive node, all the open connections in the tomcat-jdbc pool become invalid, but SQL Server doesn't report this and the connection remains 'ESTABLISHED' [see http://support.microsoft.com/kb/273673/] The stack trace we commonly see is: Caused by: java.lang.reflect.InvocationTargetException at sun.reflect.GeneratedMethodAccessor52.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:78) ... 43 more Caused by: java.sql.SQLException: Invalid state, the Connection object is closed. at net.sourceforge.jtds.jdbc.ConnectionJDBC2.checkOpen(ConnectionJDBC2.java:1634) at net.sourceforge.jtds.jdbc.ConnectionJDBC2.prepareStatement(ConnectionJDBC2.java:2328) ... 47 more Currently we have set the following params: removeAbandoned="true" logAbandoned="true" maxActive="100" maxIdle="30" minIdle="20" initialSize="20" maxWait="10000" validationQuery="SELECT count(1) from xyz" Although it's traditional to simply use 'select 1' as the validationQuery for SQL Server, we want to ensure that the query isn't actually optimized out at some point in the driver. To avoid the problem with failovers, I think we have a couple of options: #1 add the following config: testWhileIdle="true" timeBetweenEvictionRunsMillis="30000" This should evict any stale connections every 5 mins (if the idleness test is valid and it appears to be so having read the src for PooledConnection and ConnectionPool) #2 write a very simple JdbcInterceptor which checks the connection on reset: package org.apache.tomcat.jdbc.pool.interceptor; import java.sql.Statement; import org.apache.tomcat.jdbc.pool.ConnectionPool; import org.apache.tomcat.jdbc.pool.JdbcInterceptor; import org.apache.tomcat.jdbc.pool.PoolConfiguration; import org.apache.tomcat.jdbc.pool.PooledConnection; /** * If the underlying database has failed over (in active-passive configuration) * the connections need to be closed * @author k...@apache.org */ public class FailoverHandler extends JdbcInterceptor { @Override public void reset(ConnectionPool parent, PooledConnection con) { if(null == parent || null == con) return; //nothing to do PoolConfiguration config = parent.getPoolProperties(); Statement stmt = null; try { stmt = con.getConnection().createStatement(); stmt.execute(config.getValidationQuery()); stmt.close(); } catch (Exception e) { //on any exception, assume that the connection is stale //and completely remove it from pool, also force a check of all idle connections con.release(); parent.testAllIdle(); } } } With this approach we are trading a significant amount of performance for some reliability of the connection not being stale. I'm happy with this trade-off if the interceptor is correct (I know it's not bomb-proof as I really should have null guards around everything) Would anyone who has knowledge of this particular code-base be able to tell me if release() + testAllIdle() will give me the kind of guarantee I'm after or if the configuration of testWhileIdle + timeBetweenEvictionRunsMillis is just as valid? Thanks, Kev --------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@tomcat.apache.org For additional commands, e-mail: dev-h...@tomcat.apache.org
--------------------------------------------------------------------- To unsubscribe, e-mail: dev-unsubscr...@tomcat.apache.org For additional commands, e-mail: dev-h...@tomcat.apache.org
