Fixing hibernate “Cannot release connection” exception using DBCP and MySQL.

November 21, 2011 by Michael

Every 8 hours i got a Hibernate exception “Cannot release connection” within a Java application using Hibernate, Apache DBCP on Tomcat:

org.hibernate.exception.GenericJDBCException: Cannot release connection
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:29)
    ..
    ..
Caused by: java.sql.SQLException: Already closed.

Not only that the messages polluted my inbox, the exception was visible to the enduser, resulting in a HTTP 500 error. An older blog post i found suggested dismissing DBCP and using c3p0, a solution that i’m not quite found of. At least, the post helped to reproduce the problem within my development setup. The underlying problem was indeed the MySQL wait_timeout.

There’s quite a long documentation on the Tomcat JDBC Connection Pool. Although the Tomcat team recommends their own solution since Tomcat 7, i still wanted to go with DBCP.

The relevant keywords are “testOnBorrow”, “testOnReturn”, “testWhileIdle”, “validationQuery” and “timeBetweenEvictionRunsMillis”. The first 3 are boolean values. If set to true, the query given as validationQuery is executed on borrowing a connection from the pool, on returning or when idling. The first option is not an option on production use as the query is executed before each call. Although “Select 1” is probably very fast, i just don’t want to have. Also: The problem is an invalidated, idle connection so i set testWhileIdle to true. And what happened? Nothing! The problem stayed. So there is the last option timeBetweenEvictionRunsMillis which should, according to the docs, default to 5 seconds but it doesn’t. The documentation is wrong. It’s under zero, so the eviction thread that tests idle connections never run. I’ve tweeted the tomcat team, but there was no reaction.

So the correct configuration for a DBCP pool database source is:

<Resource
	type="javax.sql.DataSource"
	driverClassName="com.mysql.jdbc.Driver"
	maxActive="100"
	maxIdle="30"
	maxWait="10000"
	testOnBorrow="false"
	testOnReturn="false"
	testWhileIdle="true"
	validationQuery="Select 1"
	timeBetweenEvictionRunsMillis="1800000"
/>

This way the eviction thread runs every 30 minutes, testing idle connections with the query “Select 1” and removing them from the pool. The timeBetweenEvictionRunsMillis should not be to low. It should be adapted to the configured MySQL wait_timeout.

One comment

  1. Thomas wrote:

    the validation query depends on the used database engine:
    for more details:
    http://vondrnotes.blogspot.co......bases.html

    Posted on December 10, 2014 at 10:52 AM | Permalink
One Trackback/Pingback
  1. […] http://info.michael-simons.eu/.....ion-using-… […]

Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *