Hibernate using C3P0 Works properly on Oracle but does not close the connection with MySQL 5.6

advertisements

I am using Servlet 2.4, Hibernate 4.2.4 Final, c3p0 0.9.2.1, Tomcat 7.0.42, MySQL 5.6 & JSP.

I had completed development using Oracle 11gR2 DB but at a later point was asked to switch to MySQL as the Database.

I have a rather unusual problem at hand.

The problem is Multiple MySQL Process/Connections being created for every single DB request, which are neither closed nor returned to the pool despite issuing the SessionFactoryUtil.close(); which was not the case with Oracle DB.

I tested the exact same code on these two different Databases, i.e after executing a Function/Request (ex: Login)

The application when tested with Oracle (11gR2) the DB created a single connection and used it for all requests henceforth. SELECT * FROM V$RESOURCE_LIMIT Gives me the following Output
RESOURCE_NAME: processes
CURRENT_UTILIZATION: 32
MAX_UTILIZATION: 36
INITIAL_ALLOCATION: 300
LIMIT_VALUE: 300
No matter how many users log in the Connection pool maintains it gracefully.

Now on the other hand when the same application was run on MySQL:
I did a SHOW PROCESSLIST; on MySQL which shows two processes being created for every request; c3p0 successfully terminates one connection but the other connection remains till the DB crashes because it exceeded the max connections available.

My SessionFactoryUtil is quite simple and straightforward and is as follows: public class SessionFactoryUtil { private static SessionFactory sessionFactory;

public static SessionFactory getSessionFactory() {
    return sessionFactory = new Configuration().configure()
            .buildSessionFactory();//deprecated method not changed due to official reason
}

public Session getCurrentSession() {
    return sessionFactory.getCurrentSession();
}

public static void close() {
    if (sessionFactory != null) {
        sessionFactory.close();
    }
    sessionFactory = null;
}

My DAO Method is as follows

public User getUserByName(String userName) throws FetchException {
User user = null;
Session session = SessionFactoryUtil.getSessionFactory().getCurrentSession();
try {
    session.beginTransaction();
    user = (User) session.createQuery("from User where userName = '" + userName + "'").uniqueResult();
} catch (Exception e) {
    logger.info("UserDaoImpl -> getUserByName() : Error : " +e);
    e.printStackTrace();
} finally {
    SessionFactoryUtil.close();
}
return user;

The stack trace where c3p0 destroys a connection is as follows:

20:45:43,692 INFO com.mchange.v2.resourcepool.BasicResourcePool:1493 - A checked-out resource is overdue, and will be destroyed: [email protected] 20:45:43,692 INFO com.mchange.v2.resourcepool.BasicResourcePool:1496 - Logging the stack trace by which the overdue resource was checked-out. java.lang.Exception: DEBUG STACK TRACE: Overdue resource check-out stack trace. at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:555) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:755) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:682) at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140) at org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider.getConnection(C3P0ConnectionProvider.java:84) at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:214) at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:157) at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:67) at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1426) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:352) at com.sun.proxy.$Proxy7.beginTransaction(Unknown Source) at com.demo.access.impl.ConfDaoImp.showAllEvents(ConfDaoImp.java:939) at com.demo.business.impl.ConfServiceImpl.showAllEvents(ConfServiceImpl.java:404) at com.demo.controller.UserController.getControls(UserController.java:112) at com.demo.controller.UserController.validateUser(UserController.java:93) at com.demo.controller.UserController.process(UserController.java:42) at com.demo.controller.ApplicationServlet.process(ApplicationServlet.java:75) at com.demo.controller.ApplicationServlet.doPost(ApplicationServlet.java:53) at javax.servlet.http.HttpServlet.service(HttpServlet.java:641) at javax.servlet.http.HttpServlet.service(HttpServlet.java:722) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at com.demo.controller.LoginFilter.doFilter(LoginFilter.java:37) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:185) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:151) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:269) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:302) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

I have read almost all the question related to this particular scenario but none seems to work, or the thread was abandoned half way, or I am missing out something; could some one please help me get through with this.


This piece of your code did the trick for me:

public static void close() {
if(sessionFactory instanceof SessionFactoryImpl) {
      SessionFactoryImpl sf = (SessionFactoryImpl)sessionFactory;
      ConnectionProvider conn = sf.getConnectionProvider();
      if(conn instanceof C3P0ConnectionProvider) {
        ((C3P0ConnectionProvider)conn).close();
      }
   }
sessionFactory.close(); }

Until then Tomcat had (correctly) complained about memory leaks at each hot deployment. Thanks!