These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 
Author Message
 Post subject: MySQL connection unreleased ("sleep")
PostPosted: Mon Jul 27, 2009 12:05 pm 
Newbie

Joined: Tue Jun 30, 2009 11:03 am
Posts: 7
Hi,

I'm using:
- Tomcat 6
- Java 6 (I've also tried with Java 5)
- MySQL 5.1.33
- Hibernate 3 (I've tried Hibernate cores 3.0.5 and 3.3.2)

And I'm facing a major problem: the connections to MySQL are not released. I do open and close the session (I'm tracing it with a System.out.prinln(...) ), but when I check the connections to MySQL (SHOW PROCESSLIST;) the connections I used remain with the "command" (status I guess) "sleep" and after some page refreshment, there's an error "Too many connections".

The files I'm using:

hibernage.cfg.xml
Code:
<?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.connection.driver_class">com.mysql.jdbc.Driver</property>
      <property name="hibernate.connection.password">**********</property>
      <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/portletapp</property>
      <property name="hibernate.connection.username">user</property>
      <!-- <property name="hibernate.connection.pool_size">50</property>
      <property name="hibernate.connection.autocommit">true</property>
      <property name="hibernate.connection.release_mode">after_transaction</property> -->

      <!-- <property name="hibernate.c3p0.min_size">1</property>
      <property name="hibernate.c3p0.max_size">4</property>
      <property name="hibernate.c3p0.max_statements">10</property> -->
      <property name="hibernate.c3p0.timeout">10</property>
      
      
      <property name="hibernate.jdbc.batch_size">0</property>
      <property name="hibernate.jdbc.use_streams_for_binary">true</property>
      
      <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
      <property name="hibernate.cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
      <property name="hibernate.current_session_context_class">thread</property>
      <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
      <property name="hibernate.show_sql">true</property>
      <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
      
      
      <mapping resource="hibernate/Constant.hbm.xml" />
      <mapping resource="hibernate/Link.hbm.xml" />
      <mapping resource="hibernate/Linkcategory.hbm.xml" />
   </session-factory>
</hibernate-configuration>


_BaseRootDAO (that I modified to trace the opening and closing of sessions)
Code:
...

public abstract class _BaseRootDAO {

...

   /**
    * Return a new Session object that must be closed when the work has been completed.
    * @param configFile the config file must match the meta attribute "config-file" in the hibernate mapping file
    * @return the active Session
    */
   protected Session getSession(String configFile) {
      if (null != session && session.isOpen()) return session;
      else if (null != sessionFactory) {
         Session s = currentSession.get();
         if (null == s || !s.isOpen()) {
            s = sessionFactory.openSession();
System.out.println("<open>");
            currentSession.set(s);
         }
         return s;
      }
      else {
         Session s = currentSession.get();
         if (null == s || !s.isOpen()) {
            s = getSessionFactory(configFile).openSession();
System.out.println("<open>");
            currentSession.set(s);
         }
         return s;
      }
   }

...

   /**
    * Close all sessions for the current thread
    */
   public static void closeCurrentSession () {
      Session s = currentSession.get();
      if (null != s) {
         if (s.isOpen()){
            /* I tried: s.disconnect(); here but didn't change anything */
            s.close(); 
System.out.println(">close<");
         }
         currentSession.set(null);
      }
   }

   /**
    * Close the session
    */
   public void closeSession (Session session) {
      if (null != session && session.isOpen()){
         /* I tried: session.disconnect(); here but didn't change anything */
         session.close();
System.out.println(">close<");
      }
   }
...


}


_RootDAO (I added the methods findFiltered)
Code:
...


public abstract class _RootDAO extends hibernate.base._BaseRootDAO {

...

    protected java.util.List findFiltered(Session s, String[] propName, Object[] filter, Order order) {
   Criteria crit = s.createCriteria(getReferenceClass());

   for (int i = 0; i < propName.length; i++)
       crit.add(Expression.eq(propName[i], filter[i]));

   if (null != order)
       crit.addOrder(order);
   else
       crit.addOrder(getDefaultOrder());

   return crit.list();
    }
   
    protected java.util.List findFiltered(String[] propName, Object[] filter, Order order) {
   Session s = null;
   
   try {
       s = getSession();
       return findFiltered(s, propName, filter, order);
   } finally {
       closeSession(s);
   }
   
    }
   
    protected java.util.List findFiltered(String[] propName, Object[] filter) {
   return  findFiltered(propName, filter, null);
    }
}


LinkcategoryDAO
Code:
...

public class LinkcategoryDAO extends BaseLinkcategoryDAO implements hibernate.dao.iface.LinkcategoryDAO {

...

    @SuppressWarnings("unchecked")
    public List<Linkcategory> getLinkcategories() {
   String[] props = {Linkcategory.PROP_DISPLAY};
   Object[] values = {true};
   List<Linkcategory> linkcategories = findFiltered(props, values, getDefaultOrder());
   return linkcategories;
    }
   
...
}


LinkcategoryBO
Code:
...

public class LinkcategoryBO {
   
...
   
    public static List<Linkcategory> getLinkcategories() {
   _RootDAO.initialize();
   LinkcategoryDAO linkcategoryDAO = new LinkcategoryDAO();
   List<Linkcategory> linkcategories = linkcategoryDAO.getLinkcategories();
   return linkcategories;
    }
   
}


Trace of the execution
Code:
<open>
Hibernate: select this_.id as id14_0_, this_.name as name14_0_, this_.priority as priority14_0_, this_.display as display14_0_ from portletapp.linkcategory this_ where this_.display=? order by this_.priority asc
Hibernate: select links0_.category as category1_, links0_.id as id1_, links0_.id as id13_0_, links0_.category as category13_0_, links0_.name as name13_0_, links0_.url as url13_0_, links0_.priority as priority13_0_, links0_.display as display13_0_ from portletapp.link links0_ where links0_.category=? order by links0_.priority
Hibernate: select links0_.category as category1_, links0_.id as id1_, links0_.id as id13_0_, links0_.category as category13_0_, links0_.name as name13_0_, links0_.url as url13_0_, links0_.priority as priority13_0_, links0_.display as display13_0_ from portletapp.link links0_ where links0_.category=? order by links0_.priority
Hibernate: select links0_.category as category1_, links0_.id as id1_, links0_.id as id13_0_, links0_.category as category13_0_, links0_.name as name13_0_, links0_.url as url13_0_, links0_.priority as priority13_0_, links0_.display as display13_0_ from portletapp.link links0_ where links0_.category=? order by links0_.priority
>close<



Thank you by advance for any help you could provide,

Marc


Top
 Profile  
 
 Post subject: Re: MySQL connection unreleased ("sleep")
PostPosted: Tue Jul 28, 2009 3:01 am 
Newbie

Joined: Tue Jun 30, 2009 11:03 am
Posts: 7
What I've noticed so far is that a connection is open and remain in processlist with "sleep" status each time i call "_RootDAO.initialize();".
In an other part of my code, I call "_RootDAO.initialize();" once and load 4 records (in the trace I have 4 times <open>/>close<).
I'm trying to figure out what "_RootDAO.initialize();" do exactly but after having read it fast, it only set the configuration and create a session factory? I've also checked if there were a method like "_RootDAO.destroy();" or close or other stuff like that but nothing...


Top
 Profile  
 
 Post subject: Re: MySQL connection unreleased ("sleep")
PostPosted: Thu Jul 30, 2009 3:37 am 
Newbie

Joined: Tue Jun 30, 2009 11:03 am
Posts: 7
Connections are released when I close the session factories.


Top
 Profile  
 
 Post subject: Re: MySQL connection unreleased ("sleep")
PostPosted: Mon Aug 10, 2009 7:03 am 
Newbie

Joined: Mon Aug 10, 2009 7:00 am
Posts: 2
I am also facing the same problem, Please let me know if you have found any solution for it.

Thanks
Vishal


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 4 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.