Activiti Database Exception

[sarkar92]

I am using activiti 5.14 .
In activiti database i am getting the following exception..

Feb 22, 2014 9:05:29 PM org.springframework.transaction.support.TransactionTemplate rollbackOnException
SEVERE: Application exception overridden by rollback exception
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 8,015,918 milliseconds ago.  The last packet sent successfully to the server was 8,015,920 milliseconds ago.
### The error may exist in org/activiti/db/mapping/entity/Task.xml
### The error may involve org.activiti.engine.impl.persistence.entity.TaskEntity.selectTaskByQueryCriteria-Inline
### The error occurred while setting parameters
### SQL: select distinct RES.*              from ACT_RU_TASK RES                                    WHERE  RES.ASSIGNEE_ = ?                order by RES.ID_ asc      LIMIT ? OFFSET ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 8,015,918 milliseconds ago.  The last packet sent successfully to the server was 8,015,920 milliseconds ago.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:107)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:98)
        at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:286)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:277)


‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Recently i came to know that there is a firewall in our production environment which is dropping idle connection after some time.
and this my database config

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://10.10.8.23:3306/prod_activiti?autoReconnect=true"/>
        <property name="username" value="dbadmin" />
        <property name="password" value="SfWhfUa35Kun7Fm7" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxActive" value="300" />
        <property name="maxIdle" value="100" />
        <property name="testOnBorrow" value="true" />
        <property name="testWhileIdle" value="true" />
        <property name="maxWait" value="3000" />
        <property name="validationQuery" value="select 1" />

</bean>

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

am i doing right configuration ? please help

[sarkar92]

shall i replace my DBCP config with BoneCP config?

[hardik_kaji]

Hi,

Can you share me whole Config file?

you can add validationInterval in DBCP Config. Your connection is dropping. So you need to revalidate connection.

[sarkar92]

here it is

<code>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-2.5.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.0.xsd">

        <!– Comment this if you don't need demo data –>
<!–    <bean id="demoDataGenerator" class="org.activiti.rest.demo.DemoDataGenerator"
                init-method="init">
                <property name="processEngine" ref="processEngine" />
                <property name="createDemoUsersAndGroups" value="true" />
        <property name="createDemoProcessDefinitions" value="true" />
        <property name="createDemoModels" value="true" />
        </bean> –>

        <bean id="dbProperties"
                class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
                <property name="location" value="classpath:db.properties" />
                <!– Allow other PropertyPlaceholderConfigurer to run as well –>
                <property name="ignoreUnresolvablePlaceholders" value="true" />
        </bean>

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://************:3306/prod_activiti?autoReconnect=true"/>
        <property name="username" value="dbadmin" />
        <property name="password" value="*********" />
        <property name="poolPreparedStatements" value="true" />
        <property name="maxActive" value="300" />
        <property name="maxIdle" value="100" />
        <property name="testOnBorrow" value="true" />
        <property name="testWhileIdle" value="true" />
        <property name="maxWait" value="3000" />
        <property name="validationQuery" value="select 1" />

</bean>

        <bean id="transactionManager"
                class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
                <property name="dataSource" ref="dataSource" />
        </bean>

        <bean id="processEngineConfiguration" class="org.activiti.spring.SpringProcessEngineConfiguration">
                <property name="dataSource" ref="dataSource" />
                <property name="transactionManager" ref="transactionManager" />
                <property name="databaseSchemaUpdate" value="true" />
                <property name="mailServerHost" value="127.0.0.1" />
                <property name="mailServerPort" value="25" />
                <property name="jobExecutorActivate" value="false" />
                <property name="configurators">
          <list>
              <bean class="org.activiti.ldap.LDAPConfigurator">

                <!– Server connection params –>
  <property name="server" value="ldaps://10.0.48.10" />
                <property name="port" value="636" />
                <property name="user" value="cn=admin,ou=services,o=system" />
                <property name="password" value="********" />

                <!– Query params –>
                <property name="baseDn" value="" />
                <property name="queryUserByUserId" value="(&amp;(objectClass=inetOrgPerson)(cn={0}))" />
                <property name="queryUserByFullNameLike" value="(&amp;(objectClass=inetOrgPerson)(|({0}=*{1}*)({2}=*{3}*)))" />
                <property name="queryGroupsForUser" value="(&amp;(objectClass=groupOfNames)(member={0}))" />

                <!– Attribute config –>
                <property name="userIdAttribute" value="cn" />
                <property name="userFirstNameAttribute" value="givenName" />
                <property name="userLastNameAttribute" value="sn" />

                <property name="groupIdAttribute" value="cn" />
                <property name="groupNameAttribute" value="cn" />

              </bean>
          </list>
        </property>

        </bean>

        <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">
                <property name="processEngineConfiguration" ref="processEngineConfiguration" />
        </bean>

</beans>


</code>