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).
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]
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]
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]
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]