How to remove invalid database connection from pool

Question

I am using connection pooling of tomcat with oracle database. It is working fine, but when i use my application after a long time it is giving error that "connection reset". I am getting this error because of physical connection at oracle server closed before logical connection closed at tomcat datasource. So before getting the connection from datasource i am checking the connection validity with isValid(0) method of connection object which gives false if the physical connection was closed. But i don't know how to remove that invalid connection object from the pool.

Answer 1

This could be because on the db server, there is a timeout to not allow connections to live beyond a set time, or to die if it does not receive something saying it is still valid. One way to fix this is to turn on keepalives. These basically ping the db server saying that they are still valid connections.

This is a pretty good link on Tomcats DBCP configurations. Take a look at the section titled "Preventing dB connection pool leaks". That looks like it may be a good place to start.

Answer 2

I used validatationquery while configuring the datasource in server.xml file. It is going to check the validity of the connection by executing the query at database before giving to the application.

for Oracle

validationQuery="/* select 1 from dual */"

for MySql

validationQuery="/* ping */"

Answer 3

Try closing it and opening it if it's invalid. I mean u would reinitialize it in this way so u won't need to remove it from the pool and reuse it.

Answer 4

If we want to dispose an ill java.sql.connection from Tomcat jdbc connection pool,

we may do this explicitly in the program. Unwrap it into an org.apache.tomcat.jdbc.pool.PooledConnection, setDiscarded(true) and close the JDBC connection finally. The ConnectionPool will remove the underlying connection once it has been returned.

(ConnectionPool.returnConnection(....))

e.g. PooledConnection pconn = conn.unwrap(PooledConnection.class); pconn.setDiscarded(true); conn.close();