Hibernate pool seems to release connections

Question

I experience a strange behavior using hibernate. I have a java web application using hibernate and spring, working with MySQL database.

The symptoms: Checking the connected sessions on my sql by useing:

show processlist;

I can see the amount of connections defined at my datasource configuration, but when time passes their ID is changing, making me believe the connections are being closed and then reconnect. this behavior occurs even when there is no traffic.
I would expect that the pooled connections will keep their ID on the database.

The configuration:

<bean id="DataSource"
    class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="${url}" />
    <property name="username" value="${username}" />
    <property name="password" value="${password}" />
    <property name="maxWait" value="10" />
    <property name="maxIdle" value="5" />
    <property name="maxActive" value="0" />
    <property name="validationQuery" value="SELECT 1"/>
    <property name="testOnBorrow" value="true" />
    <property name="testOnReturn" value="true"/> 
    <property name="testWhileIdle" value="true"/>
    <property name="timeBetweenEvictionRunsMillis" value="10000"/>
    <property name="minEvictableIdleTimeMillis" value="60000"/>     
</bean>
<bean id="SessionFactory"
    class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="DataSource"></property>
    <property name="mappingResources">
        <list>
            <value>
                data/entities/entity.hbm.xml
            </value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">
                org.hibernate.dialect.MySQLDialect
            </prop>
        </props>
    </property>
</bean>

<bean id="entityDaoImpl" class="data.dao.EntityDaoImpl">
    <property name="sessionFactory" ref="SessionFactory" />
</bean>

<bean id="SessionFactory2"
    class="org.springframework.orm.hibernate3.LocalSessionFactoryBean">
    <property name="dataSource" ref="DataSource"></property>
    <property name="mappingResources">
        <list>
            <value>
                data/entities/entity2.hbm.xml
            </value>
        </list>
    </property>
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">
                org.hibernate.dialect.MySQLDialect
            </prop>
        </props>
    </property>
</bean>

<bean id="entity2DaoImpl" class="data.dao.Entity2DaoImpl">
    <property name="sessionFactory" ref="SessionFactory2" />
</bean>

My guess is that it might be connected to the fact that 2 different session factories are using the same datasource, but I belive i miss some deeper understanding (or by being right and not knowing why or by being totally wrong)

I will add also that I use spring 2.5 and Hibernate 3.1.1 running on tomcat 6. I have notice in many places people discouraging the use of hibernateTemplate but the code is useing it.

EDIT:

I opened the audit to try and figure what the connections are doing and i focused on one I saw that was closed by itself:

130806 10:58:43      13 Connect     user@localhost on database
130806 10:58:43      13 Query       SET NAMES hebrew
130806 10:58:43      13 Query       SET character_set_results = NULL
130806 10:58:43      13 Query       SHOW VARIABLES
130806 10:58:43      13 Query       SHOW COLLATION
130806 10:58:43      13 Query       SET autocommit=1
130806 10:58:43      13 Query       SET sql_mode='STRICT_TRANS_TABLES'
130806 10:58:43      13 Query       SELECT 1
130806 10:58:43      13 Query       SET autocommit=1
130806 10:58:54      13 Query       SET autocommit=1
130806 10:58:54      13 Query       SELECT 1
130806 10:58:54      13 Query       SET autocommit=1
130806 10:59:25      13 Query       SET autocommit=1
130806 10:59:25      13 Query       SELECT 1
130806 10:59:25      13 Query       SET autocommit=1
130806 11:00:27      13 Quit

And as for my understanding this shows that the problem is not on the DB side since he got the Quit command. So again I wonder is there is some keepAlive configuration I am missing in my data source configuration?

Thanks

score 1 · Answer 1 · answered Aug 05 '13 at 20:35

1

MySQL kills idle connections after a wait_timeout. Your connection pool detects that they are dead and reconnects them.

All is good...

answered Aug 05 '13 at 20:35

Boris the Spider

59,842
6
106
166

So it wont effect my application? is wait_timeout is mysql configuration? is there a problem with having multiple session factories for one data source? – Noam Shaish Aug 05 '13 at 20:38
It's normal behaviour for MySQL. Yes, `wait_timeout` is set in `my.cnf`. – Boris the Spider Aug 05 '13 at 20:39
I have check the my.cnf and saw that i am useing the default value for wait_timeout. this is 28800 seconds and i see the id's changes in matter of seconds, so this doesnt seem to be the issue. – Noam Shaish Aug 06 '13 at 07:36

score 0 · Accepted Answer · answered Aug 06 '13 at 12:26

I have found the reason for this behavior. The root is the parameter minEvictableIdleTimeMillis.

this parameter documentation says:

The minimum amount of time an object may sit idle in the pool before it is eligable for eviction by the idle object evictor (if any).

In my case meaning that after 60 seconds connections may be evicted if idle.

The parameter timeBetweenEvictionRunsMillis documentation says:

The number of milliseconds to sleep between runs of the idle object evictor thread. When non-positive, no idle object evictor thread will be run.

In my case eviction check occur every 10 seconds. another parameter missing in my configuration is numTestsPerEvictionRun documented:

The number of objects to examine during each run of the idle object evictor thread (if any).

In my case 3 connections (default) are checked for eviction.

so all in all every 10 second (except the first minute application is loaded) 3 of my 10 connection will be evicted. even though they where tested with the validationQuery while being idle (testWhileIdle is true).

I thought validationQuery query check will reset the idle time count (since a query is being sent through the connection). But I was wrong.

So finally the solution was to set minEvictableIdleTimeMillis to 600000 instead of 60000, causing my idle connections to stay longer.

Hibernate pool seems to release connections

2 Answers2