For those who plan to use Jakarta's DBCP connection pooling:

http://stealthis.athensgroup.com/presentations/White_Papers/Jakarta_Pooling.doc

I'm a bit surprised by the results of their analysis...

Did someone experience the same kind of problem ?
Is C3P0 a better alternative ?

PDF version:

http://hibernate.org/tmp/Jakarta_Pooling.pdf

I'm not really surprised. I've experienced "out of memory" and closed/shared connections with Tomcat and DBCP regularly. Because this was only in development, I simply restarted the whole mess and continued, because there's no point in debugging Jakarta software. You won't get anything done.

My personal advice is and has been for a long time: Don't use anything from Jakarta until you absolutely have to and there is no other alternative. For the connection pooling you can give the Proxool support a chance. I haven't checked it yet, but I'm sure that a dedicated and active project like Proxool is worth a try.

What about XAPool. Does anybody have experience with it in production?
http://xapool.experlog.com/

I am thinking about using it together with JOTM as a transaction manager.
http://jotm.objectweb.org/

Let me know if you have any success with XAPool, and whether or not we need a special ConnectionProvider. (Perhaps not, since it looks like it is meant to plug into an AppServer.)

I do know that XAPool plus JOTM are the basis of the DataSource pooling and transaction manager support in JettyPlus, which I think more people are going to start using now (as an alternative to TomCat).

I also know that OFBiz (http://www.ofbiz.org) is now using XAPool plus JOTM when running outside of a J2EE appserver. From following the mailing list, aside from a few hickups at the beginning, it it is proving _a lot_ better in every respect than Tyrex, but that's not hard to do...

Colin

I've tride Proxool, DBCP, and C3P0.

Proxool seemed to leave connections marked as "active" after session close, and then have errors spring up whenever one of these active connections timed out. I like its monitoring features, but I couldn't get past this active connections issue (admittedly, didn't put much effort into it other than a some google/forum/etc searching).

DBCP and C3PO I've had success with, but each dies in its own mysterious ways after serveral reloads on Tomcat. Typically I get classloader errors, which seem to be due to the fact that neither of these pools has a way to really terminate itself without shutting down the vm or "manually" finding their threads that should be shutdown when the context is destroyed.

I would LOVE to find a pool that has an api to shut down properly in this situation (the situation being that tomcat classloader has dumped all its classes, but the daemon threads created at init at still running, until they try to load some missing classes...)

I too would like to know others' experience with these pools...

thanks
tyson

We've had no luck so far with c3p0 or dbcp or the built-in pooling mechanism. It seems that after some period of hours, the connection will die and the next person to try to use the web app in question will get an app that just hangs (meanwhile an ioexception is logged).

Can someone suggest a basic set of pooling paramters for hibernate.cfg.xml that works well for them? We are using MySQL and were hoping c3p0 would be reasonably stable.

Alternatively, if there is a fix/mod/hack that someone can suggest to keep this behaviour from happening, i'm all ears!


thanks!

-dave

Neither pool performs connection validation by default. You must explicitly configure this!

What you are seeing is most likely the result of JDBC connection timeouts.

Gavin,

How do you explicitly configure connection validation??? Is this documented on the website?


thanks!


-dave

> My personal advice is and has been for a long time: Don't use anything from Jakarta until you absolutely have to and there is no other alternative.

Ehm, there is a certain product you might know that uses commons-logging, commons-lang, and commons-collection in core code ;-)

As for the article itself, I basically agree with the outlined points. Note that the exception logging has already been fixed in April 2003. And I don't consider the issue with code that continues using a connection after closing it as serious as Frank: Such a misuse of resources can cause a lot of headaches in all areas of programming. I tend to regard this as the developer's responsibility.

On the "reallyClose" issue: The comment in the Jakarta bug database says that application code is not supposed to cast to PoolableConnection and invoke that method. I tend to agree with this view: It shouldn't be necessary to tell the pool to really close the connection. On what occasions would you do this? Which kind of SQLException indicates an invalid connection, which a different kind of failure? How to do this with a different pool, anyway? A validation query on each getConnection is probably the better choice for discarding a connection if it is invalid.

The Commons DBCP team should release a follow-up to their 1.0 from August 2002, though. There have been a lot of bugfixes in the meantime, and not everyone likes to use nightly snapshots.

BTW, I've just tried to set up an XAPool 1.3.1 StandardConnectionPoolDataSource locally within an application. Fails with an NPE in XAPool code, due to a logger not set (had to track down the sources for this). It isn't obvious how to set that logger instance variable. Anyway, XAPool 1.3.1 seems unusable for this kind of usage. Note that the Commons DBCP BasicDataSource is extremely easy to use in such a bean-style manner.

Juergen

After all, isn't it peculiar that we still have to worry about a proper open source *connection pool* implementation in 2003? One could naively assume that such basic stuff would have been around for a long time already - stable and battle-proven.

Of course, one can always resort to the J2EE container's pool implementation. But it would be really nice to have a stable pool implementation for lightweight local usage within applications. I still hope that Commons DBCP will become that.

Juergen

I took a bit of a look in the code. Looks like the Null Pointer issue is happenig due to a code change in this got broken with a code change in StandardPooledConnection on Jul 22nd, when the logger actually started being used in this class, but unfortunately not all creators of this class bother to set the logger! I will follow this up on the XAPool mailing list...

Aside from that, why do you say that XAPool is not great for local usage (bean style) within an application? If you look at the StandardPoolDataSourceExample it's a fairly simple bean-style usage of the pooled datasource, I think...

Regards,
Colin

I think this might be related to your mysql url - do you have the "autoReconnect" parameter?
example, from hibernate.properties:

hibernate.connection.url jdbc:mysql://localhost:3306/mydb?autoReconnect=true

-tyson

Obviously, they don't have regression tests for that kind of usage, therefore it can't be important to them ;-) Seriously, I've specifically meant XAPool 1.3.1: Bean-style usage simply doesn't work there.

Juergen

I'm trying to handle the pooling issue by setting up tomcat to provide a (hopefully pooling) DataSource in jndi, and just have hibernate use that. Keeps all the database config in one (standard) place, since I have to speak some regular sql to this db as well. (In this case it would be silly to configure Hibernate to do its own pooling as well.)

woe #1 (actually more of a tomcat issue, but this is a common application for hibernate): Maybe my understanding of DataSources is incomplete, but I can't find a way to configure a DataSource that uses anything but a org.apache.commons.dbcp.BasicDataSourceFactory (as per the wonderfully lucid tomcat docs.) There doesn't seem to be a compatible class in C3P0. Should there be? Do I write one? Should I be using Struts' dataSources instead, or just dump the whole javax.sql mess and set up my db connections/pool the old fasioned way?

woe number two (a big reason for exploring woe#1): I have to use Oracle 8's OCI driver instead of the thin-client driver or Oracle 9, for reasons too stupid to go into here. Leaving tomcat running for any significant time clogs up the oracle server with zombie processes. It's as if either the pool isn't cleaning up old connections properly, or there's a bug in the OCI driver... which one seems more likely?