Hibernate/MySQL Connection Timeout

Question

I wrote a server-side application that powers a website and multiple mobile clients. I used Hibernate for data access. I later discovered that the app fails after a day! When I checked around online, I found out that its a well know issue with MySQL terminating a "stale" connection after 8 hours. In order to avoid this, I found many suggestions like including ?autoReconnect=true, using c3P0, etc. Since autoReconnect is officially discouraged (especially in production environment) and also because it didn't have any effect when I applied it, I decided to go for c3p0. Unfortunately, after introducing the c3p0 configuration in my hibernate.cfg.xml file, the application starts throwing a NullPointerException somewhere in my code where I called dbSession.close() This means that the HibernateUtil.getSessionFactory() actually returns null. I have added the required jars (c3p0-0.9.2-pre2.jar, hibernate-core-3.3.1.GA.jar, hibernate-c3p0-3.3.2.GA.jar, mchange-commons-java-0.2.1.jar and c3p0-oracle-thin-extras-0.9.2-pre2.jar) even though I don't think its all of them that are required. I've gone through many of the pages that talked about this issue but I'm still not able to set it up properly. Kindly help me with a "beginner-friendly" easy-to-implement, step-by-step procedure for setting up c3p0 with Hibernate. I'm using Hibernate 3.3.6 on JDK 1.6, MySQL 5.5 and I'm developing in Netbeans 7.0.

Here is my hibernate.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/religion_app</property>
    <property name="hibernate.connection.username">*****</property>
    <property name="hibernate.connection.password">*****</property>
    <property name="hibernate.show_sql">true</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <property name="hibernate.query.factory_class">org.hibernate.hql.classic.ClassicQueryTranslatorFactory</property>

    <property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
    <property name="connection.autoReconnect">true</property>
    <property name="connection.autoReconnectForPools">true</property>
    <property name="connection.is-connection-validation-required">true</property>

    <!-- configuration pool via c3p0-->
    <property name="c3p0.acquire_increment">1</property> 
    <property name="c3p0.idle_test_period">120</property> <!-- seconds --> 
    <property name="c3p0.max_size">100</property> 
    <property name="c3p0.max_statements">0</property> 
    <property name="c3p0.min_size">10</property> 
    <property name="c3p0.timeout">180</property> <!-- seconds -->
    <property name="c3p0.preferredTestQuery">select 1;</property>

    <!--Mappings go here-->
  </session-factory>
</hibernate-configuration>

Here is what I got after adding c3p0 (without slf4j):

    May 23, 2012 2:42:14 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet LoginChurch threw exception
java.lang.NullPointerException
    at com.pacesolutions.religionapp.services.LoginChurch.processRequest(LoginChurch.java:109)
    at com.pacesolutions.religionapp.services.LoginChurch.doPost(LoginChurch.java:138)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
    at java.lang.Thread.run(Thread.java:722)

May 23, 2012 2:45:13 AM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet LoginChurch threw exception
java.lang.ClassNotFoundException: org.slf4j.LoggerFactory
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1516)
    at org.apache.catalina.loader.WebappClassLoader.loadClass(WebappClassLoader.java:1361)
    at org.hibernate.connection.C3P0ConnectionProvider.<clinit>(C3P0ConnectionProvider.java:52)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at java.lang.Class.newInstance0(Class.java:372)
    at java.lang.Class.newInstance(Class.java:325)
    at org.hibernate.connection.ConnectionProviderFactory.newConnectionProvider(ConnectionProviderFactory.java:73)
    at org.hibernate.connection.ConnectionProviderFactory.newConnectionProvider(ConnectionProviderFactory.java:56)
    at org.hibernate.cfg.SettingsFactory.createConnectionProvider(SettingsFactory.java:414)
    at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:62)
    at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2009)
    at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1292)
    at org.hibernate.cfg.AnnotationConfiguration.buildSessionFactory(AnnotationConfiguration.java:859)
    at com.pacesolutions.religionapp.HibernateUtil.<clinit>(HibernateUtil.java:23)
    at com.pacesolutions.religionapp.services.LoginChurch.processRequest(LoginChurch.java:68)
    at com.pacesolutions.religionapp.services.LoginChurch.doPost(LoginChurch.java:143)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:393)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
    at org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:859)
    at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:579)
    at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1555)
    at java.lang.Thread.run(Thread.java:722)


    note The full stack trace of the root cause is available in the Apache Tomcat/6.0.26 logs.

Most of my Hibernate configuration files where generated by Netbeans. I had the database on ground then I used the Hibernate wizards in Netbeans to generate the configuration files, entity classes and mapping files from it. These were working correctly before I introduced c3p0 (but they fail after 8 hours). I use Mecurial with my project and reverting the whole project back to a time prior to when I introduced c3p0, it works again (for 8 hours). Even hand-deleting all the c3p0 configurations in hibernate.cfg.xml makes the app work again. What could I be doing wrong?

Solution 1

The jars required for C3P0 configuration are c3p0-0.9.2-pre2.jar & mchange-commons-java-0.2.1.jar. Also, additionally you need to put c3p0.properties in classpath.

Below are the properties, which should be configured while using C3P0 with Hibernate.

hibernate.cfg.xml

<property name="connection.provider_class">
                org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.acquire_increment">1</property> 
<property name="hibernate.c3p0.idle_test_period">100</property>
<property name="hibernate.c3p0.max_size">100</property> 
<property name="hibernate.c3p0.max_statements">100</property> 
<property name="hibernate.c3p0.min_size">10</property> 
<property name="hibernate.c3p0.timeout">180</property>

c3p0.properties

• You can validate connection on each checkout c3p0.testConnectionOnCheckout=true, but this is expensive operation.

• Else, you can retry to establish connection periodically.

  c3p0.acquireRetryAttempts = 4
  c3p0.acquireRetryDelay = 5000

  This will retry 4 times with a delay of 5 seconds between each consecutive attempt.

Solution 2

c3p0 has no dependency on SLF4J. If adding that library to your classpath fixed your issue, that is interesting, but not so easy to explain.

A stack trace of the original NPE tomcat logged when you had problems would be helpful. (I'm c3p0's developer.)

Note that c3p0.properties needs to be at the top level of the CLASSPATH of your app, which may not be where other config files are located. c3p0.properties is loaded as a ClassLoader resource.