How to remove invalid database connection from pool

java oracle tomcat connection-pooling

19,196

Solution 1

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 */"

Solution 2

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.

19,196

Dyapa Srikanth

Technology Lead in Infosys, Hyderabad.

Updated on June 13, 2022

Comments

Dyapa Srikanth almost 2 years

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.
Dyapa Srikanth almost 13 years

if(!con.isValid(30)){ con.close(); con = ds.getConnection(); } Who knows whether the second one is valid one.
Dyapa Srikanth almost 13 years

con = ds.getConnection(); if(!con.isValid(30)) con = ds.getConnection(); return con; If i do like this i will get a new connection from the pool and i am returning this new connection to the calling place and that function closes the connection after the use. Here i am not returning invalid connection so that it abandoned after some time that what i configured at pooling configuration file (server.xml/context.xml)
cjstehno almost 13 years

Yes, I have run into this before. You can configure the connection pool to check for dead connections using a SQL query (validationQuery) on a specified interval. This is part of the DBCP configuration. You want to avoid checking in your code as your code should be unaware of the pooling or the specific database.
Dyapa Srikanth almost 13 years

I already configured it, but when i call con.isValid() it is not giving any thing.
John Kane almost 13 years

@cjstehno yes I agree. You should not be checking the status of the connections in your pool in the code. What type of pool are you using?
Dyapa Srikanth almost 13 years

public static Connection getConnection(){ Connection con = null; try{ if(ds == null) loadDataSource(); con = ds.getConnection(); }catch(Exception e){ e.printStackTrace(); throw new DBUtilException("Unable to get database connection object. Cause: "+e.getMessage()); } return con; } What i need to do.
John Kane almost 13 years

That does not really seem like the place to fix this issue. You want to configure the connection pool (or connection manager depending on where you are getting the connections from) to manage the connections for your application.
John Kane almost 13 years

what is the timeout for keeping connections on your server? Could that be less than what you have set? I think the remove abandoned timeout is in seconds, I am not sure what oracle defaults to, but if that time is less that could cause the problem.
John Kane almost 13 years

you probably do not want to post your dbs: url,username, and password. You probably want to edit your comment or change it on your server.
Dyapa Srikanth almost 13 years

i am just configuring the server.xml file of tomcat for the connection pooling which is given at tomcat documentation. Is it Connection pooling or not? If not what i have to do?
John Kane almost 13 years

Yeah, that should be. I was referring to the code you posted above. I just meant that that is not where you would want to fix this issue.
John Kane almost 13 years

This looks like a good resource for the DBCP configuration: commons.apache.org/dbcp/configuration.html
John Kane almost 13 years

These are a couple of other useful links that will help: devdaily.com/blog/post/java/… and java2s.com/Open-Source/Java-Document/…