JDBC MySQL - Connection closed

Question

I have a web application. I use JPA (Hibernate as vendor), the application run on glassfish server. After several minutes using the application (including operations that retrieve and save entities to the DB) i get this error:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection
root cause

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Could not open connection
root cause

org.hibernate.exception.GenericJDBCException: Could not open connection
root cause

java.sql.SQLException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start:
root cause

javax.resource.spi.ResourceAllocationException: Error in allocating a connection. Cause: java.lang.RuntimeException: Got exception during XAResource.start:
root cause

com.sun.appserv.connectors.internal.api.PoolingException: java.lang.RuntimeException: Got exception during XAResource.start:
root cause

com.sun.appserv.connectors.internal.api.PoolingException: java.lang.RuntimeException: Got exception during XAResource.start:
root cause

java.lang.RuntimeException: Got exception during XAResource.start:
root cause

javax.transaction.xa.XAException: com.sun.appserv.connectors.internal.api.PoolingException: javax.resource.spi.LocalTransactionException: No operations allowed after connection closed.

I am using JTA transaction manager. Every function in my Service layer has the @Transactional annotation. This is my the persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">
    <persistence-unit name="myPU" transaction-type="JTA">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <jta-data-source>jdbc/myDataSource</jta-data-source>

        <class>org.company.entities.User</class>
            <!-- OTHER CLASSES -->

        <exclude-unlisted-classes />

        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
            <property name="hibernate.transaction.flush_before_completion" value="true" />
            <property name="hibernate.show_sql" value="false" />
            <property name="hibernate.connection.release_mode" value="auto"/>
            <property name="hibernate.current_session_context_class" value="jta"/>
            <property name="hibernate.transaction.auto_close_session" value="true"/>
<!--             <property name="hibernate.transaction.flush_before_completion" value="true"/> -->
<!--             <property name="hibernate.transaction.factory_class" value="org.hibernate.transaction.JTATransactionFactory"/> -->
        </properties>

    </persistence-unit>
</persistence>

I am using this mysql connector: mysql-connector-java-5.1.22-bin.jar Why am i getting this error? Where am i or JPA is closing the connection?

EDIT: Removed details of class "BaseEntityDAO" - not relevant. My data source is configured via the admin console of the glassfish: In the JDBC Connection pool:

Resource Type: javax.sql.DataSource
DataSource class name: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
Im using the default Pool settings of glassfish - don't know if it matters but one of the default settings is "Idle Timeout" and its default value is 300 sec.
In the Additional Properties i hve the following properties: portNumber, databaseName, serverName, user, password

In the JDBC Resource:

JNDI Name: jdbc/myDataSource
Pool Name: The pool name of the above JDBC Connection Pool

This is my spring configuration:

<jee:jndi-lookup id="entityManagerFactory" jndi-name="persistence/myPersistenceUnit" />

<bean
    class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor">
    <property name="persistenceUnits">
        <map>
            <entry key="myPersistenceUnit" value="persistence/myPersistenceUnit" />
        </map>
    </property>
</bean>

<tx:annotation-driven transaction-manager="transactionManager" />
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTransactionManager" />

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/myDataSource" />

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
    <constructor-arg ref="dataSource" />
</bean>

How local DataSource to MySQL is configured? How connection pool is configured? Is the connection timeout set to default value in MySQL Server (8 hrs) or it was changed? Does this exception keep showing or it disappears after several refreshes? PS: BaseEntityDAO does not relate to the problem, it doesn't control transactions/connections, so you may remove it from the question. — Stanislav Bashkyrtsev
thanks for the reply - i have edited the question with neew information according to you request. In addition - the exception keeps showing no matter what (unless i stop and start the application). - Thank you — Mr T.

Alexandre Lavoie Alexandre Lavoie · Accepted Answer · 2012-11-15T08:32:15

You could get a try with C3P0 with this config :

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="c3p0.testConnectionOnCheckout">false</property>
<property name="c3p0.min_size">2</property>
<property name="c3p0.max_size">10</property>
<property name="c3p0.timeout">300</property>
<property name="c3p0.max_statements">50</property>
<property name="c3p0.idleTestPeriod">300</property>

I'm using this configuration in many projects since years.

JDBC MySQL - Connection closed

1 Answers