Currently we are migrating some of existing applications from Websphere to Tomcat. We are using spring high concurrency connection pool for DB interactions.
We are facing an issue in Tomcat related to stale connections. Whenever, there is a response back from server indicating that the connection is already closed, connection pool should reject the connection. However, in our case its continuing with the same connection (application is throwing exceptions) and the pool is not getting purged.
We parallely tested the same on websphere(with the same driver & database instance) and we found that this issue is not present on websphere.
For now, we havent put any validation query / configuration as we are not sure if we have permissions to execute custom SQLs .
Is there any solution(s) where we can purge the stale connection and the entire pool when we encounter a "connection already closed error" basing on the error code received back in SQL exception ?
Currently this issue is occurring when interacting with Sybase DB. Not sure about other databases.
Below are the configuration details:
<Resource auth="Container" driverClassName="com.sybase.jdbc2.jdbc.SybDriver" size="38" minIdle="8" maxWait="8" minEvictableIdleTimeMillis="1800" timeBetweenEvictionRunsMillis="300" name="jdbc/<<lookup name>>" password="XX" username="XX" type="javax.sql.DataSource" url="jdbc:sybase:Tds:<server>:<port>/<dbname>" defaultTransactionIsolation="2" jdbcInterceptors="StatementCache(prepared=true,cal lable=true,max=8)"/>
Error Log from Tomcat:
java.sql.SQLException: JZ006: Caught IOException: com.sybase.jdbc2.jdbc.SybConnectionDeadException: JZ0C0: Connection is already closed. at com.sybase.jdbc2.jdbc.ErrorMessage.createIOEKilled ConnEx(ErrorMessage.java:753)
at com.sybase.jdbc2.jdbc.ErrorMessage.raiseErrorCheck Dead(ErrorMessage.java:794)