1 Reply Latest reply on Jan 20, 2016 4:39 AM by jimmy001

    JDBCConnectionException on Task persistence Unit

    mdegan
    mdegan Nov 26, 2013 2:40 AM

      Hi,

       

      I am using MySQL as the DB and it has the default wait_timeout of 8 hours. Every day when I open my test environment and check the list of tasks, I get the following error:

       

      javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: The last packet successfully received from the server was 318,819,449 milliseconds ago. The last packet sent successfully to the server was 318,819,449 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

          org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1377) org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1300) org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:273) org.jbpm.task.service.persistence.TaskPersistenceManager.queryWithParameters(TaskPersistenceManager.java:352) org.jbpm.task.service.persistence.TaskPersistenceManager.queryWithParametersInTransaction(TaskPersistenceManager.java:295) org.jbpm.task.service.persistence.TaskPersistenceManager.queryWithParametersInTransaction(TaskPersistenceManager.java:317) org.jbpm.task.service.TaskServiceSession.getTasksAssignedAsPotentialOwnerByStatus(TaskServiceSession.java:800) org.jbpm.task.service.local.LocalTaskService.getTasksAssignedAsPotentialOwnerByStatus(LocalTaskService.java:177)

       

      I understand that I can increase the server wait_timeout to a longer time, but I believe that its just a workaround. I am trying to search for info to resolve this but havent been successful. The steps that I have tried are:

      1) Added C3P0 connection pooling to the Task persistence unit in persistence.xml

      2) Used autoReconnect=true for the DB name in persistence.xml

      3) Used c3p0 properties for testConnectionOnCheckout and preferredTestQuery. These are expensive and performance impacting, so I used this only for testing

       

      Even after using connection pooling, this issue still happens everyday and I need to restart the app to get it to work again. I am looking for any suggestions to sort this out so that I can think of a production build. Will appreciate any help.

       

      BTW, here is my vanilla Persistence.xml that I use:

       

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

      <persistence version="1.0"

        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence

                                       http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd

                                       http://java.sun.com/xml/ns/persistence/orm

                                       http://java.sun.com/xml/ns/persistence/orm_1_0.xsd"

        xmlns:orm="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

        xmlns="http://java.sun.com/xml/ns/persistence">

       

       

        <persistence-unit name="org.jbpm.runtime" transaction-type="JTA">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <jta-data-source>jdbc/jbpm-ds</jta-data-source>

       

       

        <mapping-file>META-INF/JBPMorm.xml</mapping-file>

        <mapping-file>META-INF/ProcessInstanceInfo.hbm.xml</mapping-file>

       

       

        <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

        <class>org.drools.persistence.info.SessionInfo</class>

        <class>org.drools.persistence.info.WorkItemInfo</class>

       

       

        <class>org.jbpm.process.audit.ProcessInstanceLog</class>

        <class>org.jbpm.process.audit.NodeInstanceLog</class>

        <class>org.jbpm.process.audit.VariableInstanceLog</class>

       

       

        <properties>

        <property name="hibernate.max_fetch_depth" value="3" />

        <property name="hibernate.hbm2ddl.auto" value="update" />

        <property name="hibernate.show_sql" value="false" />

        <property name="hibernate.transaction.manager_lookup_class"

        value="org.hibernate.transaction.BTMTransactionManagerLookup" />

        <property name="hibernate.jndi.class" value="bitronix.tm.jndi.BitronixInitialContextFactory"/>

        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />

        </properties>

        </persistence-unit>

       

        <persistence-unit name="org.jbpm.task">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <mapping-file>META-INF/Taskorm.xml</mapping-file>

       

       

        <class>org.jbpm.task.Attachment</class>

        <class>org.jbpm.task.Content</class>

        <class>org.jbpm.task.BooleanExpression</class>

        <class>org.jbpm.task.Comment</class>

        <class>org.jbpm.task.Deadline</class>

        <class>org.jbpm.task.Delegation</class>

        <class>org.jbpm.task.Escalation</class>

        <class>org.jbpm.task.Group</class>

        <class>org.jbpm.task.I18NText</class>

        <class>org.jbpm.task.Notification</class>

        <class>org.jbpm.task.EmailNotification</class>

        <class>org.jbpm.task.EmailNotificationHeader</class>

        <class>org.jbpm.task.PeopleAssignments</class>

        <class>org.jbpm.task.Reassignment</class>

        <class>org.jbpm.task.Status</class>

        <class>org.jbpm.task.Task</class>

        <class>org.jbpm.task.TaskData</class>

        <class>org.jbpm.task.SubTasksStrategy</class>

        <class>org.jbpm.task.OnParentAbortAllSubTasksEndStrategy</class>

        <class>org.jbpm.task.OnAllSubTasksEndParentEndStrategy</class>

        <class>org.jbpm.task.User</class>

       

        <properties>

        <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />

        <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" />

        <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/dbname?autoReconnect=true" />

        <property name="hibernate.connection.username" value="xxxx" />

        <property name="hibernate.connection.password" value="yyyyy" />

        <property name="hibernate.connection.autocommit" value="false" />

        <property name="hibernate.max_fetch_depth" value="3" />

        <property name="hibernate.hbm2ddl.auto" value="update" />

        <property name="hibernate.show_sql" value="false" />

        </properties>

        </persistence-unit>