Thursday, October 15, 2009

Hibernate, C3p0, and MySQL

In today's applications, database connection pooling is a common best practice. It improves performance by reusing existing connections as establishing a new one is fairly expensive. C3p0 is one of the well-known database connection pool libraries. And Hibernate is now the de facto standard for object-relational mapping (ORM) in Java world. Here is a sample configuration to use C3p0 in Hibernate for MySQL database:
<!-- Basic -->
<property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
<property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
<property name="hibernate.connection.url">jdbc:mysql://127.0.0.1/test</property>
<property name="hibernate.connection.username">username</property>
<property name="hibernate.connection.password">password</property>
<property name="hibernate.connection.default_schema">test</property>

<!-- C3p0 -->
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.timeout">120</property>
<property name="hibernate.c3p0.max_size">25</property>
<property name="hibernate.c3p0.min_size">1</property>
<property name="hibernate.c3p0.max_statement">0</property>
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
Two properties to put focus on are idle_test_period and timeout. The property idle_test_period defines the interval to test unchecked-out connections while timeout is the time unused connections can live in the pool. However, you might see Communication Link Failure errors occasionally in your log, even though it's configured to have connections tested. Listed are some possible causes:

Network
Make sure your system allows connecting to the database. For example, your unix system may have /etc/hosts.allow or /etc/hosts.deny turned on for access control. Or your firewall could block or kill your connections based on the rules.

Database
Check the system configuration on your database. MySQL has a wait_timeout setting which will close the connections idle over a period of time. If your idle_test_period and timeout are greater than MySQL wait_timeout (28800 seconds by default), your unused connections will have chance to be reset by MySQL server before tested or timed out by C3p0.

Configuration
Choose the connection pool library for your configuration. By default, Hibernate should pick the right one for you. But, if there are more than one connection pool libraries on your classpath, it may not be the way you expect. Hibernate allows you to specify the library by setting the property provider_class as follows:
<property name="hibernate.connection.provider_class">
  org.hibernate.connection.C3P0ConnectionProvider
</property>
Of course, you could just simply remove other connection pool libraries.

Reference
How to configure the C3P0 connection pool

6 comments:

  1. Hi Sheng - Came through your reply at stackoverflow - thanks I will try this out and let you know how it worked out for me.

    ReplyDelete
  2. Hello Vatsalad, hope it works for you. Good luck!

    ReplyDelete
  3. I have changed accordingly, hope so it works!!

    ReplyDelete
  4. Hello Sheng,

    Thank you very much for this answer. It works great when I run my application on my dev machine, which is also where mysql is.
    How ever, when I run it on my staging environment, the Communication Link Failure continues to show up.
    I even changed the mysql timeout to 30 secs and the c3p0 idle test period to 20 secs, to reproduce it quicker; and sure enough Communication Link Failure occurs.
    The application can communicate without problems with the db (via hibernate), but then after the mysql idle time is reached, it fails.
    Any other suggestions?

    Thank you very much!

    ReplyDelete
  5. I've been fiddling with this for months and I can't get rid of the dreaded [c3p0] Another error has occurred [ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. ] which will not be reported to listeners!

    Can you explain what you meant about networks? My application is running on tomcat and the mysql server is on the same machine so I can't imagine that is an issue

    Cheers

    Kris

    ReplyDelete
  6. is there a way to log all the queries?

    ReplyDelete