The Jakarta Commons team is pleased to announce the release of version 1.2 of the Jakarta Commons DBCP component, and the Jakarta Commons Pool component. Commons-DBCP provides database connection pooling services. Together with Commons-Pool it is the default JNDI datasource provider for Tomcat.

New Features in 1.2

• Performance optimizations for the PoolableConnectionFactory of BasicDataSource.
• Add BasicDataSourceFactory.createDataSource(Properties properties) for creating a BasicDataSource.
• Add initialSize parameter to do pre-loading of the connection.
• Refactoring of the DelegatingStatement classes.
• Make some properties dynamic (changeble at runtime), this will be completed in next release.
• Improvements to the prepared statement pooling.
• Driver specific changes/optimizations for: Oracle, Informix, DaffodilDB.
• Several fixes for PerUserPoolDataSource.
• PoolingDriver uses now a PoolGuardConnectionWrapper by default. If access to the underlying connection is needed, you have to enable it:
         PoolingDriver.setAccessToUnderlyingConnectionAllowed(true)
• This release contains bug fixes to all known issues.

For more information check out:

Commons DBCP home page (release notes)

Commons Pool home page (release notes)

As we know tomcat comes with these libraries under common lib. so can we just replace those with the new ones ???
I can always find out - just wanted to know if any one is aware of it..
Thanks Apache Group

I've got 2 suggestions (Implemented in my project) through ConnectionFactory:
* an XADataSource wrapper (for XAPool by example)
* the possibility to define init statement to run when a Connection is created. I use it to define RDBMS session environnement like Date Format.

I don't already try the last version, but I'm happy with previous, Good job.

Tx

Although Commons DBCP gets quite a lot of bashing recently (particularly from the Hibernate side), I've actually been happily using it in various environments.

A particular nice thing is the ease of access to the underlying native JDBC objects, if necessary. And of course, it allows for very easy bean-style configuration through its BasicDataSource (for example, in Spring).

A new feature that I'd really like to see in DBCP 1.3 is a time-to-live for Connections. In particular with MySQL, such a feature is very helpful, because MySQL Connections can start showing strange behavior when the underlying connection has been terminated and implicitly reconnected.

Note that such a setting should not regard whether the connection has been active or idle: After the specified amount of time, it should be closed and removed from the pool in any case.

A validation query doesn't help in the MySQL scenario: The driver will automatically reconnect and successfully execute the query, but the Connection still has a chance of remaining in an inconsistent state. This won't happen on every reconnect, just on some.

Interestingly, various versions of MySQL Connector (the JDBC driver) show different behavior in that respect. We repeatedly observed the above both with 3.0.9 and 3.0.11, though, after running an app for a couple of days.

Two connection pools that provide such a time-to-live are Resin's ("max-pool-time") and Proxool ("maximum-connection-lifetime"). We're using the former now for MySQL. Unfortunately, Proxool doesn't provide a bean-style DataSource yet... (but will do so in the upcoming 0.9 release).

Juergen

Excellent info Juergen, thanks. Over the last year we too have encountered the exact same problems that you describe with the MySQL driver, both with and without Commons DBCP, although usually worse with it. A big +1 from me on Juergen's suggestion to add a max-time-to-live setting in DBCP. Until then, I'll withold my hope on using Tomcat JNDI data sources for MySQL connections. All it takes is a single Memorial weekend of application inactivity to see my app erroring out on the connection(s).

Two points:
- Future releases of Tomcat will use the latest DBCP/Pool releases. You can try copying the DBCP/Pool jars into existing tomcat installations, and it will probably work just fine (no interface changes that would break things).

- Tomcat uses DBCP in its examples and makes it available by default, but it's by no means required. You can use any database connection pool software, including home grown ones, with tomcat's JNDI services.

BTW, the corresponding enhancement request that I submitted a while ago:

http://issues.apache.org/bugzilla/show_bug.cgi?id=28211

Juergen

Excellent info Juergen, thanks. Over the last year we too have encountered the exact same problems that you describe with the MySQL driver, both with and without Commons DBCP, although usually worse with it. A big +1 from me on Juergen's suggestion to add a max-time-to-live setting in DBCP. Until then, I'll withold my hope on using Tomcat JNDI data sources for MySQL connections. All it takes is a single Memorial weekend of application inactivity to see my app erroring out on the connection(s).

The only reason you see this issue with MySQL is because the server has a forced-disconnect after 8 hours of idle time (which is of course configurable).

Either configure your connection pool to not let connections sit idle for more than 8 hours (which is a resource drain, unfortunately DBCP has no easy way to do this, as is already covered in this thread), or configure "wait_timeout" on the server to be something higher, like 2^32 if you care more about connections living a long time rather than claiming resources while they're idle.

Most people who run with MySQL and JDBC in heavy production use connection pools that support max idle time scenarios, and they only let connections go idle for a few seconds. It only takes a few milliseconds to setup a new MySQL connection, so you're not saving any _time_ by using a connection pool (what you _are_ doing is throttling resource usage).

Note that you'd have the _exact_ same problems with any other vendors' JDBC driver if someone happened to pull your network cable, or restart the database. You just happen to notice it more with MySQL because of the server's forced disconnect (designed to deal with poorly written PHP apps and C programs which sometimes didn't clean up their connections when they were done with them).

In any case, there is absolutely no guarantee that a connection pulled from a connection pool will be live, which is why you need to deal with SQLExceptions if you want database connection failures to be transparent to your users. The JDBC specification doesn't specify that, and if you think it through, there's no way that guarantee could be made, due to transactional recovery requirements (unless you wanted a full-blown logging transaction manager on the _client_ side that could replay transactions from a failed connection).

Although Commons DBCP gets quite a lot of bashing recently (particularly from the Hibernate side), I've actually been happily using it in various environments.A particular nice thing is the ease of access to the underlying native JDBC objects, if necessary. And of course, it allows for very easy bean-style configuration through its BasicDataSource (for example, in Spring).A new feature that I'd really like to see in DBCP 1.3 is a time-to-live for Connections. In particular with MySQL, such a feature is very helpful, because MySQL Connections can start showing strange behavior when the underlying connection has been terminated and implicitly reconnected.Note that such a setting should not regard whether the connection has been active or idle: After the specified amount of time, it should be closed and removed from the pool in any case.A validation query doesn't help in the MySQL scenario: The driver will automatically reconnect and successfully execute the query, but the Connection still has a chance of remaining in an inconsistent state.

The autoReconnect feature is not on by default in the JDBC driver for MySQL (and has never been the default, because it's dangerous in some situations, and this is spelled out in the documentation), and in fact we actively discourage using it. Unfortunately, DBCP doesn't have the kind of options that allow you to have "max-time-to-live" in an easy fashion like other connection pools (as you've pointed out), so it's kind of hard to make DBCP work in the "long-lived" connection scenario.

Unfortunately, many third-party organizations (such as Apache) have "latched on" to autoReconnect and tell people to use it in their documentation without explaining what the full ramifications are.

Has anyone used XAPool from ObjectWeb? How does it compare to DBCP (except for the LGPL license)?

It does seem to have connection lifetime support.

I work with XADataSource, and I try XAPool. I encounter problems with the Pool of XAPool (I wrote the list on a paper somewhere). So I combine DBCP (Pool) and XAPool (XADataSource).