After deploying a Google GWT application using Hibernate and C3P0 connection pooling backed by a MySQL database server to a test server running Tomcat everything seemed to run correctly. But letting the server run over night a login attempt failed the next day. A quick search showed me that I am not the only one on this planet who ran into the following error:
org.hibernate.exception.JDBCConnectionException: could not execute query [...] com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: [...]
This problem occurs because MySQL drops idle JDBC connection after 8 hours, but the C3P0 connection pooling mechanism still considers these connection as alive and valid.
I found several possible solution on the Internet. Some people recommend to set the “autoReconnect” parameter in the JDBC URL, but this is not recommended by the MySQL team.
Others recommend to set the C3P0 properties accordingly in order to check and close idle database connections. In most cases the proposed solution looks like the “official” configuration provided by the Hibernate crew:
<property name="c3p0.min_size">3</property> <property name="c3p0.max_size">25</property> <property name="c3p0.acquire_increment">1</property> <property name="c3p0.max_statements">50</property> <property name="c3p0.timeout">14400</property> <property name="c3p0.idle_test_period">3600</property>
But as I have found in many, many comments in discussion groups this also did not work in a large number of cases. In order to get C3P0 connection pooling in Hibernate3 work correctly with a MySQL database one important property setting is still missing. I found this hint in a comment to the above “official” configuration. In addition one has to explicitly set the connection provider class:
<property name="connection.provider_class"> org.hibernate.connection.C3P0ConnectionProvider </property>
After setting this property the above C3P0 configuration works correctly. At least for me.
The other major factor is the fact that change is uncomfortable. Doing what we’ve learned through training is not easy. What we are currently doing is comfortable to us and easy to stick with because it’s what we are used to. Here’s the clincher, we stick with it even though we know the better thing we really should be doing.