HibernateDaoSupport quartz mysql db connections are not being put back into the pool

[alfred0]

Hi all, we are having a problem with a quartz job.
The database connections are not being put back into the pool when the job ends.
The web part of the application use the same daos and does not leaks connections.
We isolate the problem in a minimal example.
Any help will be apreciated.


Spring / quartz configuration:

    
<bean id="testJob" class="com.clarin.gateway.job.TestJob">
    <property name="ahTransaccionDAO" ref="ahTransaccionDAO"></property>
</bean>

<bean id="jobDetailTestJob" class="org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean" >
    <property name="concurrent" value="false"/>
    <property name="targetObject" ref="testJob"/>
    <property name="targetMethod" value="test"/>
</bean>

<bean id="simpleTriggerTestJob" class="org.springframework.scheduling.quartz.SimpleTriggerBean">
    <property name="jobDetail" ref="jobDetailTestJob"/>
    <property name="startDelay" value="10000"/>
    <property name="repeatInterval" value="30000"/>
</bean>

<bean class="org.springframework.scheduling.quartz.SchedulerFactoryBean">
    <property name="triggers">
        <list>
            <ref bean="simpleTriggerTestJob"/>
        </list>
    </property>
</bean>

Job code:

    
public class TestJob {

    private Logger logger = org.slf4j.LoggerFactory.getLogger(this.getClass());

    private AHTransaccionDAO ahTransaccionDAO ;

    public TestJob() {
        logger.info("NotificarCambioEstadoJob()") ;
    }
    
    public AHTransaccionDAO getAhTransaccionDAO() {
        return ahTransaccionDAO;
    }

    public void setAhTransaccionDAO(AHTransaccionDAO ahTransaccionDAO) {
        this.ahTransaccionDAO = ahTransaccionDAO;
    }

    public void test () {
        logger.info("TestJob.test " + getAhTransaccionDAO().getServerTimestamp()) ;
    }

}

DAO code:

   
public interface AHTransaccionDAO extends BaseDAO<AHTransaccion, Long> {
...

public class HibernateAHTransaccionDAO extends HibernateBaseDAO<AHTransaccion, Long> implements AHTransaccionDAO {
...

public abstract class HibernateBaseDAO <T, ID extends Serializable> extends HibernateDaoSupport implements BaseDAO<T, ID> {
    public void update(T entity) {
        this.getHibernateTemplate().update(entity);  
    }
...

Jboss output:

17:51:45,118 INFO  [Server] JBoss (MX MicroKernel) [4.2.3.GA (build: SVNTag=JBoss_4_2_3_GA date=200807181417)] Started in 49s:879ms
17:52:13,361 TRACE [JBossManagedConnectionPool] supplying new ManagedConnection: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@888443[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@a38887 handles=0 lastUse=1293051133361 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@587d59 txSync=null]
17:52:13,361 TRACE [JBossManagedConnectionPool] Got connection from pool org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@888443[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@a38887 handles=0 lastUse=1293051133361 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@587d59 txSync=null] [InUse/Available/Max]: [2/18/20]
17:52:13,361 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:52:15.0
17:52:43,369 TRACE [JBossManagedConnectionPool] supplying new ManagedConnection: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1a11cee[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@72878a handles=0 lastUse=1293051163369 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@120795b txSync=null]
17:52:43,369 TRACE [JBossManagedConnectionPool] Got connection from pool org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1a11cee[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@72878a handles=0 lastUse=1293051163369 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@120795b txSync=null] [InUse/Available/Max]: [3/17/20]
17:52:43,369 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:52:45.0
17:53:13,362 TRACE [JBossManagedConnectionPool] supplying new ManagedConnection: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1913bf7[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1f7e75f handles=0 lastUse=1293051193362 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@771836 txSync=null]
17:53:13,362 TRACE [JBossManagedConnectionPool] Got connection from pool org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1913bf7[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@1f7e75f handles=0 lastUse=1293051193362 permit=true trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@146d454 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@11e0abe xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@771836 txSync=null] [InUse/Available/Max]: [4/16/20]
17:53:13,362 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:53:15.0

[dr_pompeii]

Hello alfred0

The database connections are not being put back into the pool when the job ends.

I am not an expert about this, but are you referring about this problem about pools by the follow output?

17:52:13,361 TRACE [JBossManagedConnectionPool]
supplying new ManagedConnection:

and by

17:52:13,361 TRACE [JBossManagedConnectionPool]
Got connection from pool org.jboss.resource.connectionmanager.TxConnectionM anager...

I did realize in your code you are not showing none configuration about pools there you should have configured many important parameters

[alfred0]

Hello dr_pompeii, thanks for answering.

Yes, tracing the evolution of the DataSource pool in the JBoss logs I see that the connections are not been returned to the pool (when used from the quartz jobs).

17:51:45,118 INFO  [Server] JBoss (MX MicroKernel) [4.2.3.GA (build: SVNTag=JBoss_4_2_3_GA date=200807181417)] Started in 49s:879ms
17:52:13,361 TRACE [...] supplying new ManagedConnection: ...
17:52:13,361 TRACE [...] Got connection from pool ... [InUse/Available/Max]: [2/18/20]
17:52:13,361 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:52:15.0
17:52:43,369 TRACE [...] supplying new ManagedConnection: ...
17:52:43,369 TRACE [...] Got connection from pool ... [InUse/Available/Max]: [3/17/20]
17:52:43,369 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:52:45.0
17:53:13,362 TRACE [...] supplying new ManagedConnection: ...
17:53:13,362 TRACE [...] Got connection from pool ... [InUse/Available/Max]: [4/16/20]
17:53:13,362 INFO  [STDOUT] INFO : com.clarin.gateway.job.TestJob - TestJob.test 2010-12-22 17:53:15.0

For example, when the daos are used from de web applications the JBoss logs are

11:24:45,435 TRACE [JBossManagedConnectionPool] supplying ManagedConnection from pool: ...
11:24:45,435 TRACE [JBossManagedConnectionPool] Got connection from pool ... [InUse/Available/Max]: [3/17/20]
11:24:45,435 TRACE [JBossManagedConnectionPool] putting ManagedConnection back into pool ...
11:24:45,435 TRACE [JBossManagedConnectionPool] Returned connection to pool ... [InUse/Available/Max]: [2/18/20]

11:24:45,435 TRACE [JBossManagedConnectionPool] supplying ManagedConnection from pool: ...
11:24:45,435 TRACE [JBossManagedConnectionPool] Got connection from pool ... [InUse/Available/Max]: [3/17/20]
11:24:45,435 INFO  [STDOUT] INFO : 11:24:45,482 TRACE [JBossManagedConnectionPool] putting ManagedConnection back into pool ...
11:24:45,482 TRACE [JBossManagedConnectionPool] Returned connection to pool ... [InUse/Available/Max]: [2/18/20]

The data source definition is the MySQL JBoss example with minimal modifications ( default pool parameters )

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml 63175 2007-05-21 16:26:06Z rrajesh $ -->
<!--  Datasource config for MySQL using 3.0.9 available from:
http://www.mysql.com/downloads/api-jdbc-stable.html
-->

<datasources>
        <local-tx-datasource>
                <jndi-name>MySqlDS</jndi-name>
                <connection-url>jdbc:mysql://...:3306/asd</connection-url>
                <driver-class>com.mysql.jdbc.Driver</driver-class>
                <user-name>asd</user-name>
                <password>asd</password>
                <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sort
er-class-name>
                <metadata>
                        <type-mapping>mySQL</type-mapping>
                </metadata>
        </local-tx-datasource>
</datasources>

Regards

[alfred0]

The problem was in our implementation of HibernateBaseDAO.

We add two methods to access HQL functions that use HibernateDaoSupport.getSession (maybe that was the problem).

	public Timestamp getServerTimestamp() {
		Object obj = getSession().createSQLQuery("SELECT CURRENT_TIMESTAMP").uniqueResult() ;
		return (Timestamp)obj;
	}

	public Date getServerDate() {
		Object obj = getSession().createSQLQuery("SELECT CURRENT_DATE").uniqueResult() ;
		return (Date)obj;
	}

We remove those methods and the leaking stop.

Just for completeness sake, to access HQL functions we create a dummy entity (related to a dummy table with one record).

Regards