Problem 'Connection is invalid.' on Activiti DB

[timo_rohrberg]

Hello everybody,

I'm addressing you guys today with a problem that we unfortunately cannot solve even though investigating and trying different configuration parameters to an unknow extent.

When setting our system internally using the Activiti workflow engine under heavy load, we get the error "Connection is invalid." in different situations when Activiti tries to query its database tables or even write updates to them. The error happens with different SQL statements executed by Activiti and depending on which one it is, the consequences are quite problematic. In some cases this can even lead to not starting a workflow when one should be started.

The stack trace always looks like this:
<java>
### Cause: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
   at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:122)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
   at org.activiti.engine.impl.db.DbSqlSession.selectListWithRawParameter(DbSqlSession.java:443)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:434)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:429)
   at org.activiti.engine.impl.db.DbSqlSession.selectList(DbSqlSession.java:406)
   at org.activiti.engine.impl.persistence.entity.ExecutionEntityManager.findChildExecutionsByParentExecutionId(ExecutionEntityManager.java:113)
   at org.activiti.engine.impl.persistence.entity.ExecutionEntity.ensureExecutionsInitialized(ExecutionEntity.java:694)
   at org.activiti.engine.impl.persistence.entity.ExecutionEntity.createExecution(ExecutionEntity.java:272)
   at org.activiti.engine.impl.persistence.entity.ExecutionEntity.createExecution(ExecutionEntity.java:75)
   at org.activiti.engine.impl.pvm.runtime.AtomicOperationTransitionCreateScope.execute(AtomicOperationTransitionCreateScope.java:36)
   at org.activiti.engine.impl.interceptor.CommandContext.performOperation(CommandContext.java:97)
   at org.activiti.engine.impl.jobexecutor.AsyncContinuationJobHandler.execute(AsyncContinuationJobHandler.java:35)
   at org.activiti.engine.impl.persistence.entity.JobEntity.execute(JobEntity.java:85)
   at org.activiti.engine.impl.persistence.entity.MessageEntity.execute(MessageEntity.java:34)
   at org.activiti.engine.impl.cmd.ExecuteAsyncJobCmd.execute(ExecuteAsyncJobCmd.java:52)
   at org.activiti.engine.impl.interceptor.CommandInvoker.execute(CommandInvoker.java:24)
   at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:57)
   at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:37)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:40)
   at org.activiti.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:35)
   at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.executeJob(ExecuteAsyncRunnable.java:95)
   at org.activiti.engine.impl.asyncexecutor.ExecuteAsyncRunnable.run(ExecuteAsyncRunnable.java:50)
   at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
   at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
   at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Error accessing PooledConnection. Connection is invalid.
   at org.apache.ibatis.datasource.pooled.PooledConnection.checkConnection(PooledConnection.java:254)
   at org.apache.ibatis.datasource.pooled.PooledConnection.invoke(PooledConnection.java:243)
   at com.sun.proxy.$Proxy2.prepareStatement(Unknown Source)
   at sun.reflect.GeneratedMethodAccessor26.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:483)
   at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
   at com.sun.proxy.$Proxy2.prepareStatement(Unknown Source)
   at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:79)
   at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
   at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:58)
   at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:76)
   at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:61)
   at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)
   at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)
   at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
   at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
   … 25 common frames omitted
<java>

Our idea is that somehow the threads in the Activiti thread pool take to long to be executed and in the meantime MyBatis invalidates the DB connection. But increasing the parameter 'jdbcMaxCheckoutTime' completely blocks our system. We simply don't have any further idea how to approach this error.

Does anyone have an idea what can be the reason for that?

We appreciate any hint.

Regards
Timo

[trademak]

Hi Timo,

Which connection pool are you using? The connection pool should be able to test the connection before it's provided to be used by the Activiti Engine.

Best regards,

[timo_rohrberg]

Hello Tijs,

thank you for your reply. We are just using the built-in connection pool of the MyBatis 3.3.0 which is drawn-in via the dependencies of the Activiti Engine. From what our analysis of the log files so far revealed, we think that the connection is already provided to the Activiti Engine by the connection pool and then the Activiti thread holding that connection does not get to run early enough. When the OS scheduler make the thread executing, the connection is already invalid.

Do you have any idea what can be done in those situations? Or is that just a bottleneck in the execution performance that we need address in different ways?

We appreciate any hint…

Best regards
Timo Rohrberg