Activiti Process Engine Looses MySQL DB Connection?

[mgriffith]

Any thoughts would be appreciated on this vexing problem. We are using Activiti-5.6 in our web application and it is configured with Spring.  We are using MySQL 5.1.41 on Ubuntu (AWS). JDBC Connector is mysql-connector-java-5.1.6. Our process engine looses it's connection to the database connection pool, causing our process to fail.  This seems to happen when the process engine has been at rest for some period of time – like overnight, and we try to start a process. The process fails on the first user task.

The error is:

2:17:13,413  ERROR [extensions.webscripts.AbstractRuntime] Exception from executeScript - redirecting to status template error: Error configuring AutoCommit.  Your dr$org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: f$        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:47)        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.<init>(JdbcTransaction.java:15)        at org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory.newTransaction(JdbcTransactionFactory.java:15)        at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:77)        at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:32)        at org.activiti.engine.impl.db.DbSqlSession.<init>(DbSqlSession.java:84)        at org.activiti.engine.impl.db.DbSqlSessionFactory.openSession(DbSqlSessionFactory.java:77)        at org.activiti.engine.impl.interceptor.CommandContext.getSession(CommandContext.java:177)        at org.activiti.engine.impl.persistence.AbstractManager.getSession(AbstractManager.java:56)        at org.activiti.engine.impl.persistence.AbstractManager.getDbSqlSession(AbstractManager.java:52)        at org.activiti.engine.impl.persistence.entity.UserManager.findUserById(UserManager.java:49)        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:35)        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:23)        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)        at org.activiti.engine.impl.IdentityServiceImpl.checkPassword(IdentityServiceImpl.java:90)        at org.activiti.rest.auth.ActivitiBasicHttpAuthenticatorFactory.doAuthenticate(ActivitiBasicHttpAuthenticatorFactory.java:61)        at org.springframework.extensions.webscripts.AbstractBasicHttpAuthenticatorFactory$BasicHttpAuthenticator.authenticate(AbstractBasicHttpAuthenticatorFactory.ja$        at org.springframework.extensions.webscripts.PresentationContainer.executeScript(PresentationContainer.java:67)        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:333)        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:189)        at org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:118)        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)        at java.lang.Thread.run(Thread.java:636)Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was75159 seconds ago.The last packet sent suc$        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)        at java.lang.reflect.Constructor.newInstance(Constructor.java:532)        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3246)        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1917)        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2536)        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4874)        at sun.reflect.GeneratedMethodAccessor184.invoke(Unknown Source)        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)        at java.lang.reflect.Method.invoke(Method.java:616)        at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:225)        at $Proxy20.setAutoCommit(Unknown Source)        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:42)        … 35 moreCaused by: java.net.SocketException: Broken pipe‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The process engine is configured in Spring as such:

   <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean">        <property name="jndiName">           <value>java:/comp/env/jdbc/activiti</value>        </property>    </bean>     <bean id="wf-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="wf-transactionManager" />      <property name="databaseSchemaUpdate" value="true" />      <property name="jobExecutorActivate" value="true" />      <property name="jobExecutor" ref="jobExecutor"/>   </bean>   <bean id="processEngine" class="org.activiti.spring.ProcessEngineFactoryBean">      <property name="processEngineConfiguration" ref="processEngineConfiguration" />   </bean>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And the JNDI Connection is configured in Tomcat 6 as:

<Resource          name="jdbc/activiti"          auth="Container"         type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"         url="jdbc:mysql://localhost:3306/activiti?autoReconnect=true"         username="root"         password=""         maxActive="200"         maxIdle="10"         testOnBorrow="true" maxWait="-1"/>‍‍‍‍‍‍‍‍‍‍‍‍

I've used Spring/JPA for many web applications using MySQL and never had this problem.  Any ideas on how to debug/solve this DB connection error would be greatly appreciated.

[trademak]

Hi,

Please spend some time searching for older forum posts first. A similar discussion can be found here for example:

http://forums.activiti.org/en/viewtopic.php?f=6&t=2260

Best regards,

[mgriffith]

Hi,

Please spend some time searching for older forum posts first. A similar discussion can be found here for example:

http://forums.activiti.org/en/viewtopic.php?f=6&t=2260

Best regards,

I wasn't sure if this was related, as I have my Spring Transaction manager set to DataSourceTransactionManger instead of the JPA transaction manager.

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

Do you still believe it is the same issue?  I looked at the documentation for the process engine config transactionsExternallyManaged and there is no documentation on it.  What does it do?

Thanks in advance,
MG

[mgriffith]

Hi,

Please spend some time searching for older forum posts first. A similar discussion can be found here for example:

http://forums.activiti.org/en/viewtopic.php?f=6&t=2260

Best regards,

Setting the property <property name="transactionsExternallyManaged" value="true" /> did not solve the problem. I'm still getting this error:

Sep 19, 2011 12:05:25 PM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.transaction.TransactionException: Error configuring AutoCommit.  Your driver may not support getAutoCommit() or setAutoCommit(). Requested setting: false.$
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.setDesiredAutoCommit(JdbcTransaction.java:47)
        at org.apache.ibatis.transaction.jdbc.JdbcTransaction.<init>(JdbcTransaction.java:15)
        at org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory.newTransaction(JdbcTransactionFactory.java:15)
        at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSessionFromDataSource(DefaultSqlSessionFactory.java:77)
        at org.apache.ibatis.session.defaults.DefaultSqlSessionFactory.openSession(DefaultSqlSessionFactory.java:32)
        at org.activiti.engine.impl.db.DbSqlSession.<init>(DbSqlSession.java:84)
        at org.activiti.engine.impl.db.DbSqlSessionFactory.openSession(DbSqlSessionFactory.java:77)
        at org.activiti.engine.impl.interceptor.CommandContext.getSession(CommandContext.java:177)
        at org.activiti.engine.impl.persistence.AbstractManager.getSession(AbstractManager.java:56)
        at org.activiti.engine.impl.persistence.AbstractManager.getDbSqlSession(AbstractManager.java:52)
        at org.activiti.engine.impl.persistence.entity.UserManager.findUserById(UserManager.java:49)
        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:35)
        at org.activiti.engine.impl.cmd.CheckPassword.execute(CheckPassword.java:23)
        at org.activiti.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24)
        at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:42)
        at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:33)
        at org.activiti.engine.impl.IdentityServiceImpl.checkPassword(IdentityServiceImpl.java:90)
        at org.activiti.rest.auth.ActivitiBasicHttpAuthenticatorFactory.doAuthenticate(ActivitiBasicHttpAuthenticatorFactory.java:61)
        at org.springframework.extensions.webscripts.AbstractBasicHttpAuthenticatorFactory$BasicHttpAuthenticator.authenticate(AbstractBasicHttpAuthenticatorFactory.java:15$
        at org.springframework.extensions.webscripts.PresentationContainer.executeScript(PresentationContainer.java:67)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:333)
        at org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:189)
        at org.springframework.extensions.webscripts.servlet.WebScriptServlet.service(WebScriptServlet.java:118)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
        at java.lang.Thread.run(Thread.java:636)
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was63904 seconds ago.

Any other thoughts would be appreciated.

[gilbo]

Hi,

the error message

The last packet successfully received from the server was63904 seconds ago.

is generated by a timeout on MySql, which is by default 8 hours.
Please note that usage of "autoReconnect" parameter in jdbc configuration is not recommended (see http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html, it only states that when a statement fails because of a disconnected connection, it returns a SQLException that the code must handle.
If you can change the server configuration, you can increase the "wait_timeout" parameter (in my.cnf), otherwise you can try to add a "validationQuery" to the Tomcat resource:


<Resource
         name="jdbc/activiti"
         auth="Container"
         type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
         url="jdbc:mysql://localhost:3306/activiti?autoReconnect=true"
         username="root"
         password=""
         maxActive="200"
         maxIdle="10"
        
         validationQuery="Select 1"

         testOnBorrow="true" maxWait="-1"/>


PS: there is also a autoReconnectForPools parameter, that should ping the server before each statement, but never used.

HTH
gilbo