MySQL Exception

[hethcox]

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 swapped out and can't recover when it's swapped back in. Can I set the timer on the connection that Activiti uses? 

Best,
John

Here's my engine file:
  <bean id="processEngineConfiguration" class="org.activiti.engine.impl.cfg.StandaloneProcessEngineConfiguration">
    <property name="history" value="audit" />
    <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/activiti?autoReconnect=true" />
    <property name="jdbcDriver" value="com.mysql.jdbc.Driver" />
    <property name="jdbcUsername" value="trakkit" />
    <property name="jdbcPassword" value="——" />
   
    <property name="databaseSchemaUpdate" value="true" />
   
    <property name="jobExecutorActivate" value="false" />
…   

Here's the exception:

Jun 21, 2013 8:25:45 AM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: 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 successfu
lly received from the server was39292 seconds ago.The last packet sent successfully to the server was 39292 seconds ago, which  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.
### The error may exist in org/activiti/db/mapping/entity/User.xml
### The error may involve org.activiti.engine.impl.persistence.entity.UserEntity.selectUserById
### 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 was39292 seconds ago.The last packet sent successfully to the server was 39292 seconds ago, which  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.
        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.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:59)
        at org.activiti.engine.impl.db.DbSqlSession.selectOne(DbSqlSession.java:307)
        at org.activiti.engine.impl.persistence.entity.UserManager.findUserById(UserManager.java:54)
        at org.activiti.engine.impl.persistence.entity.UserManager.checkPassword(UserManager.java:107)
        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:37)
        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:24)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:60)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
        at org.activiti.engine.impl.IdentityServiceImpl.checkPassword(IdentityServiceImpl.java:90)
        at com.george.bpm.server.TrakkitServiceImpl.validateLogin(TrakkitServiceImpl.java:100)
        at sun.reflect.GeneratedMethodAccessor225.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
        at java.lang.reflect.Method.invoke(Unknown Source)
        at com.google.gwt.user.server.rpc.RPC.invokeAndEncodeResponse(RPC.java:561)
        at com.google.gwt.user.server.rpc.RemoteServiceServlet.processCall(RemoteServiceServlet.java:208)
        at com.google.gwt.user.server.rpc.RemoteServiceServlet.processPost(RemoteServiceServlet.java:248)
        at com.google.gwt.user.server.rpc.AbstractRemoteServiceServlet.doPost(AbstractRemoteServiceServlet.java:62)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:1852)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
        at java.lang.Thread.run(Unknown Source)
Caused by: org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCo
mmit(). Requested setting: false.  Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the serv
er was39292 seconds ago.The last packet sent successfully to the server was 39292 seconds ago, which  is longer than the server configured value of 'w
ait_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.
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:102)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:135)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:58)
        at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:279)
        at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:69)
        at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:56)
        at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
        at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:141)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:105)
        at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:81)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:101)
        … 37 more

[frederikherema1]

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.

[fcauti]

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

[frederikherema1]

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

[fcauti]

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.

[fcauti]

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>

[jbarrez]

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

[fcauti]

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

[trademak]

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,

[fcauti]

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>