• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JDBC DBCP connection time out

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello all,

I am having problems when using DBCP to connect to my database. The problem is that the connections in the pool get expired after my program stays stale for about 9 or 10 hours. The next time I try to connect I received the error on connection timeout.

I know, in case of DBCP, it is possible to validate the connections taken out of the pool on the time of borrowing from or storing to the pool. I have set testOnBorrow equal to true for my connection pool and also the validationQuery is set to "SELECT 1 FROM mytable" where mytable is the name of a valid table in my DB. Nonetheless, still the problem seems to be there and the connections get time out after a certain period of time.

I have also read about autoReconnectForPools which is supposed to do a reconnection to the DB when the connections in the pool are timed out. yet, setting it to true had no effect either.

It is worth mentioning that, usually after two or three trials to connect to the server a fresh copy of a connection object gets returned from the pool and I am able to query the database from then on. Leaving the system stale for a while would however make the problem occur again.

I have read that C3P0 is probably a better way of implementing connection pools, nonetheless I would like to get this problem solved with DBCP first before deciding on whether or not to switch to C3P0.

Could anybody suggest any method to test whether the changes that I am making to the pool really have some effects on it or not? Basically, how can I check if the validation really happens? also, does anyone have any suggestion on a potential solution to this?

thanks,
Nemo
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

also the validationQuery is set to "SELECT 1 FROM mytable"

This should do the trick.

Yes, it is possible that your connections become invalid when they are not active, but it is the purpose of validationQuery to detect and correct this.
Have you checked that you return all connections to the pool as soon as you have finished using them? That is a prerequisite for this mechanism.
If you keep some connections local in stead of returning them, the validationQuery mechanism will not help you.


 
Doe, a deer, a female deer. Ray, a pockeful of sun. Me, a name, I call my tiny ad ...
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic