Win a copy of Terraform in Action this week in the Cloud forum!
  • 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:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

connection pool recovery

 
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I guess if you do some refreshes (many, until all closed connections are removed from the pool), it will start to get new connections.

But anyway, I like your suggestion of a feature to "recovery", or, best, to restart the connection pool, but I'm not sure how to do that (security implications and etc).

Try first the refresh thing.

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hi;
i am using jforum 2.0.2 with a connection pool configured to work over a tomcat datasource. (code is similar to the jforum datasource support that was published in a later version if i recall correctly).

anyway,
i noticed that when the DB machine is crashing (its on a different machine from the forum application). after the machine comes back online, the forum application cannot function correctly it just hangs when accessed and it seems there is no recovery of the pool connections.

is this the expected behavior? is this fixed in 2.1.x?
thanks in advance.
[originally posted on jforum.net by gkatz]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In this regard, I happen to have to develop a DB Pool and stability and recovery issues are tricky to get right, and even then you cannot cover 100% of the cases.

We went through a period of time when our DB hosts were "less than reliable" and we ended up developing, in our opinion, quite a stable pool. Some of the things that we do are:

.- Check each non-used connection every X milliseconds with a dummy SQL String (select sysdate from dual, for example). If the statement fails, drop the connection and get another.

.- If all the connections are broken (probable indication of DB host being down), shut down the pool and try again first with a single Connection when the pool is accesed again. If/When that single conn works, start the pool again.

.- After X seconds of no pool usage, close all the connections and shutdown the pool until first access again. It saves connections from timing out when there's no access, as some DB close connections when not in use.

.- Check that the connections you request to be created are really created and take that number into account for the max-connections number. Some times DB take a long time to return a new connection, or they just plain hang, so if you don't take into account the number of "connections requested but not yet created", you might overwhelm your DB and create too many threads.

.- Recycle the connections after X number of usages. Why? Some drivers/DBs don't clean properly all objects and they throw errors in the long run ("too many cursors open" anyone? ;) )

With all of that, there's still a rare case where we have had the pool get in trouble, due to a JDBC driver bug, but DB hosts are stopped daily for maintenance and applications recover nicely.

Cheers!
[originally posted on jforum.net by GreenEyed]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
it seems jdbc 4.0 can handle invalid connection inside a pool.
u can read: http://java.sys-con.com/read/111252.htm
however, jdk 6 is still far away...
[originally posted on jforum.net by gkatz]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Ummm,

Well, we'll just have to wait until JDK6 is here, until vendors decide to provide full support for JDBC4.0 and then until they implement JDBC drivers without bugs... ummm

The things they talk about in that article about pooling should have been there "ages" ago. But what can you expect if nowadays vendors still cannot implement right the "simple" pools for their own databases :roll:

When they do it right, I'll be happy to drop down my implementation of a pool , but until then...

PD: BTW, thanks for the link, it's good to know.
[originally posted on jforum.net by GreenEyed]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about C3P0? http://sourceforge.net/projects/c3p0

Rafael
[originally posted on jforum.net by Rafael Steil]
 
Migrated From Jforum.net
Ranch Hand
Posts: 17424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Rafael Steil wrote:How about C3P0? http://sourceforge.net/projects/c3p0

Rafael



It might be an option, even though I have not used it. We use ours for our Oracle applications, but when moving some projects to Hibernate, I opted for Proxool, as I found more easily the "how-to integrate it with Hibernate" documentation.

I'm using it now with Hibernate+Oracle on the "private forums" implementation I talked to you about, so when I have time to play "dirty tricks" with it, I'll let you know if it behaves appropriately. Dirty tricks being over-stress testing it to see if the number of connections opened keeps within the limits, unplugin the network and plug it again to corrupt all connections, etc.. :twisted:
[originally posted on jforum.net by GreenEyed]
 
reply
    Bookmark Topic Watch Topic
  • New Topic