Clustered/Shared Database Configuration and Connection Pooling

I have a configuration using Camunda 7.4.5 and the WildFly 8 EE distro. I have three Camunda hosts using a shared database represented by four (4) MariaDB hosts in a Galera cluster. I am using HAProxy as a poor man’s load balancer in front of the Camunda servers as well as providing the option of using it to access the database.

However, when I set up the datasource to use the HAProxy URL, I get errors like those shown in the stack trace below. When I switch the datasource to use the “sequential” mode of the MariaDB Connector/J driver (which requires you to list each actual host in the connection URL), it seems to work okay.

Also, in this distro, the WildFly datasource connection pool is not configured by default. Is that because the default database is H2, or we should not use connection pooling, or we should not use it in certain circumstances? What are your recommendations for the use of connection pools, in particular for WildFly and MariaDB/MySQL clustered databases?

One final question would be what, if any, recommendations would you have when using a network load balancer (Cisco Content Switch) in front of the Camunda process engines and/or clustered database?

Thanks.

Michael

2016-04-09 20:46:23,533 ERROR [org.camunda.bpm.engine.context] (default task-32) ENGINE-16004 Exception while closing command context: 
### Error querying database.  Cause: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/ProcessDefinition.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ProcessDefinitionEntity.selectProcessDefinitionsByQueryCriteria_mysql
### The error occurred while executing a query
### Cause: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine: org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine
### The error may exist in org/camunda/bpm/engine/impl/mapping/entity/ProcessDefinition.xml
### The error may involve org.camunda.bpm.engine.impl.persistence.entity.ProcessDefinitionEntity.selectProcessDefinitionsByQueryCriteria_mysql
### The error occurred while executing a query
### Cause: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)
at org.camunda.bpm.engine.impl.db.sql.DbSqlSession.selectList(DbSqlSession.java:87) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectListWithRawParameter(DbEntityManager.java:166) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:158) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.db.entitymanager.DbEntityManager.selectList(DbEntityManager.java:150) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.persistence.entity.ProcessDefinitionManager.findProcessDefinitionsByQueryCriteria(ProcessDefinitionManager.java:60) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.ProcessDefinitionQueryImpl.executeList(ProcessDefinitionQueryImpl.java:252) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.AbstractQuery.evaluateExpressionsAndExecuteList(AbstractQuery.java:186) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.AbstractQuery.executeSingleResult(AbstractQuery.java:207) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.AbstractQuery.execute(AbstractQuery.java:167) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.interceptor.CommandExecutorImpl.execute(CommandExecutorImpl.java:24) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:95) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.interceptor.JtaTransactionInterceptor.execute(JtaTransactionInterceptor.java:58) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.interceptor.ProcessApplicationContextInterceptor.execute(ProcessApplicationContextInterceptor.java:66) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.impl.AbstractQuery.singleResult(AbstractQuery.java:130) [camunda-engine-7.4.5-ee.jar:7.4.5-ee]
at org.camunda.bpm.engine.rest.impl.ProcessDefinitionRestServiceImpl.getProcessDefinitionByKey(ProcessDefinitionRestServiceImpl.java:54) [classes:]
at sun.reflect.GeneratedMethodAccessor279.invoke(Unknown Source) [:1.7.0_45]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_45]
at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_45]
at org.jboss.resteasy.core.ResourceLocatorInvoker.createResource(ResourceLocatorInvoker.java:81) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:108) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.core.ResourceLocatorInvoker.invokeOnTargetObject(ResourceLocatorInvoker.java:135) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.core.ResourceLocatorInvoker.invoke(ResourceLocatorInvoker.java:103) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:356) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.core.SynchronousDispatcher.invoke(SynchronousDispatcher.java:179) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.plugins.server.servlet.ServletContainerDispatcher.service(ServletContainerDispatcher.java:220) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:56) [resteasy-jaxrs-3.0.10.Final.jar:]
at org.jboss.resteasy.plugins.server.servlet.HttpServletDispatcher.service(HttpServletDispatcher.java:51) [resteasy-jaxrs-3.0.10.Final.jar:]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [jboss-servlet-api_3.1_spec-1.0.0.Final.jar:1.0.0.Final]
at io.undertow.servlet.handlers.ServletHandler.handleRequest(ServletHandler.java:86) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:130) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at org.camunda.bpm.engine.rest.filter.CacheControlFilter.doFilter(CacheControlFilter.java:41) [classes:]
at io.undertow.servlet.core.ManagedFilter.doFilter(ManagedFilter.java:60) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.FilterHandler$FilterChainImpl.doFilter(FilterHandler.java:132) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.FilterHandler.handleRequest(FilterHandler.java:85) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.security.ServletSecurityRoleHandler.handleRequest(ServletSecurityRoleHandler.java:62) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.ServletDispatchingHandler.handleRequest(ServletDispatchingHandler.java:36) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at org.wildfly.extension.undertow.security.SecurityContextAssociationHandler.handleRequest(SecurityContextAssociationHandler.java:78)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.security.SSLInformationAssociationHandler.handleRequest(SSLInformationAssociationHandler.java:131) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.security.ServletAuthenticationCallHandler.handleRequest(ServletAuthenticationCallHandler.java:57) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.security.handlers.AbstractConfidentialityHandler.handleRequest(AbstractConfidentialityHandler.java:46) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.security.ServletConfidentialityConstraintHandler.handleRequest(ServletConfidentialityConstraintHandler.java:64) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.security.handlers.AuthenticationMechanismsHandler.handleRequest(AuthenticationMechanismsHandler.java:58) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.security.CachedAuthenticatedSessionHandler.handleRequest(CachedAuthenticatedSessionHandler.java:70) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.security.handlers.SecurityInitialHandler.handleRequest(SecurityInitialHandler.java:76) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at org.wildfly.extension.undertow.security.jacc.JACCContextIdHandler.handleRequest(JACCContextIdHandler.java:61)
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.server.handlers.PredicateHandler.handleRequest(PredicateHandler.java:43) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.ServletInitialHandler.handleFirstRequest(ServletInitialHandler.java:261) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.ServletInitialHandler.dispatchRequest(ServletInitialHandler.java:248) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.ServletInitialHandler.access$000(ServletInitialHandler.java:77) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.servlet.handlers.ServletInitialHandler$1.handleRequest(ServletInitialHandler.java:167) [undertow-servlet-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.server.Connectors.executeRootHandler(Connectors.java:199) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at io.undertow.server.HttpServerExchange$1.run(HttpServerExchange.java:761) [undertow-core-1.1.8.Final.jar:1.1.8.Final]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_45]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_45]
at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_45]
Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:154)
at org.apache.ibatis.transaction.managed.ManagedTransaction.openConnection(ManagedTransaction.java:87)
at org.apache.ibatis.transaction.managed.ManagedTransaction.getConnection(ManagedTransaction.java:61)
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:279)
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:72)
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59)
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)
... 61 more
Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:jboss/datasources/ProcessEngine
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:442)
at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:421)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:515)
at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:146)
... 71 more
Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (0 [ms])
at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:511)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getTransactionNewConnection(AbstractPool.java:541)
at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:442)
at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:379)
... 74 more

Hi Michael,

in your stacktrace the root cause is:

Is 0ms not a little bit short? Or what does 0ms mean in this configuration?

As this is a wildfly/datasource configuration issue not directly related to camunda I’m not sure I can help but maybe you could post your datasource configuration?

Cheers,
Sebastian