topic Re: MySQL Exception in Alfresco Archive

MySQL Exception

hethcox — Fri, 21 Jun 2013 18:08:11 GMT

I'm running Activiti 5.11 on Server 2008 with MySQL 5.6. I wrote a custom app that uses Activiti engine. After the system sets idle for several hours I get the exception below. I've tried monkeying around with the database URL, but no luck. Can anyone suggest a solution? I think the DB is getting sw

Re: MySQL Exception

frederikherema1 — Tue, 25 Jun 2013 09:16:54 GMT

Activiti can use any connection-pool you provide. Not sure what the issue exactly is here, but I'm guessing you have a job (eg. timer, async activity) that is stuck or doing a large amount of work and causes the timeout to expire.

Or, if it's indeed, the swapping of the DB that is the issue (and the DB-connection-pool contains bad connections), you should consider looking deeper into possible connection-pools (e.g.. DBCP) and their possibilities regarding validity-checing and so on.

Re: MySQL Exception

fcauti — Mon, 12 Aug 2013 07:58:22 GMT

I have the same problem with the same configuration (except that I use Activiti 5.12 and that I have also hibernate running in the same application and connecting to the same DB). Each server that remains idle for some hours then it return this error on the first call to an activiti operation. Is there something in the ibatis configuration?

Thanks

Re: MySQL Exception

frederikherema1 — Mon, 12 Aug 2013 13:55:00 GMT

What kind of datasource/connection-pool are you using? Make sure your pool is capable of evicting stale connections or renewing them transparently…

Re: MySQL Exception

fcauti — Mon, 12 Aug 2013 15:21:46 GMT

Actually I am using the default MyBatis pool using the jdbc parameters, as it is written in the documentation: "The data source that is constructed based on the provided JDBC properties will have the default MyBatis connection pool settings".
The activiti.cfg.xml is:
<code>
<bean id="processEngineConfiguration" class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">
    <property name="jdbcUrl" value="jdbc:mysql://<host>:3306/<dbschema>?autoReconnect=true" />
    <property name="jdbcDriver" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUsername" value="<user>" />
    <property name="jdbcPassword" value="<password>" />
    <property name="databaseSchemaUpdate" value="false" />
    <property name="jobExecutorActivate" value="false" />
</bean>
</code>
I hope the problem is not that there are two connection pools acting on the same database (the hibernate one and the ibatis one), they should be independent.

Re: MySQL Exception

fcauti — Tue, 13 Aug 2013 08:10:45 GMT

To be more precise, the error occurs when the process engine is idle for about 8 hours on the server.
It happens at the first operation when I query the list of processes for a given key:
<code>
List<ProcessDefinition> allVersionsOfThisProcess = repositoryService.createProcessDefinitionQuery().processDefinitionKey(processDefinitionKey).list();
</code>
The initial part of the log is:
<java>
2013-08-13 07:53:53:561 - ERROR - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.
Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.
Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException:
The last packet successfully received from the server was60900 milliseconds ago.The last packet sent successfully to the server was 60900 milliseconds ago, which is longer than the server conf                        igured 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.
### The error may exist in org/activiti/db/mapping/entity/ProcessDefinition.xml
### The error may involve org.activiti.engine.impl.persistence.entity.ProcessDefinitionEntity.selectProcessDefinitionsByQueryCriteria_mysql
### The error occurred while executing a query
### Cause: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit. Your driver may not support getAutoCommit() or setAutoCommit()                        . Requested setting: false. Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was60900                         milliseconds ago.The last packet sent successfully to the server was 60900 milliseconds ago, which is longer than the server configured value of 'wait_time                        out'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for cli                        ent timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95)
        at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:302)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:293)
        at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:283)
        at org.activiti.engine.impl.persistence.entity.ProcessDefinitionEntityManager.findProcessDefinitionsByQueryCriteria(ProcessDefinitionEntityManager.j                        ava:52)
        at org.activiti.engine.impl.ProcessDefinitionQueryImpl.executeList(ProcessDefinitionQueryImpl.java:260)
        at org.activiti.engine.impl.AbstractQuery.execute(AbstractQuery.java:139)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:61)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:31)
        at org.activiti.engine.impl.AbstractQuery.list(AbstractQuery.java:113)
</java>

Re: MySQL Exception

jbarrez — Wed, 14 Aug 2013 10:11:34 GMT

Seems like you need to add a 'keepalive' query on the connection pool, cause that is what seems to be happening.

Re: MySQL Exception

fcauti — Mon, 19 Aug 2013 08:51:59 GMT

Thank you for the answer.
How can I add a keepalive query on the MyBatis connection pool? I use activiti defining the jdbc properties in the activiti.cfg.xml.
In the user guide I read that the parameters that I can add to tweak the connection pool are just these: jdbcMaxActiveConnections, jdbcMaxIdleConnections, jdbcMaxCheckoutTime, jdbcMaxWaitTime.

Thank you
Federico

Re: MySQL Exception

trademak — Wed, 21 Aug 2013 07:16:47 GMT

Hi Frederico,

The best solution is to use a connection pooling framework like c3p0 or an alternative and to prevent stale connections there.

Best regards,

Re: MySQL Exception

fcauti — Thu, 22 Aug 2013 09:47:10 GMT

Hi Trademak,

Thank you, now I use c3p0 with "idleConnectionTestPeriod" parameter and it seems to not give that problem again.
For info that problem happens when the connection is idle for more than 8h in MySql.

My configuration now is:

<code>
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" >
   <property name="driverClass" value="com.mysql.jdbc.Driver" />
   <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/…?autoReconnect=true" />
   <property name="user" value="…" />
   <property name="password" value="…" />
      <property name="minPoolSize" value="2" />
      <property name="maxPoolSize" value="20" />
      <property name="acquireIncrement" value="2" />
      <!– <property name="testConnectionOnCheckin" value="true" /> –>
   <property name="idleConnectionTestPeriod" value="3600" />
   <property name="maxIdleTimeExcessConnections" value="300" />
</bean>

<bean id="processEngineConfiguration" class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">

     <property name="dataSource" ref="dataSource" />
     <property name="databaseSchemaUpdate" value="false" />
     <property name="jobExecutorActivate" value="false" />

</bean>
</code>

Re: MySQL Exception

jbarrez — Tue, 03 Sep 2013 09:31:18 GMT

When connections get dropped after a certain amount of time, most of the time it is the check for idle connections which needs to be implemented.