JDBC Connection Issue

Flowable Engine
1

Hi Team

we are getting below issues in our BPMN microservices Can you suggest what configuration we should have for JDBC connection pools so that these issues can be avoided. We are using common DB for multiple BPMN flowable flows.

Error Details
engine bpmn during async job acquisition: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:306)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:378)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:137)
at org.flowable.common.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:56)
at org.flowable.common.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
at org.flowable.common.engine.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
at org.flowable.job.service.impl.asyncexecutor.AcquireAsyncJobsDueRunnable.acquireAndExecuteJobs(AcquireAsyncJobsDueRunnable.java:84)
at org.flowable.job.service.impl.asyncexecutor.AcquireAsyncJobsDueRunnable.run(AcquireAsyncJobsDueRunnable.java:62)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30000ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:676)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:190)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:155)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:263)

Regards
Sameer

2

Can you share your connection properties, for example:

# Spring Datasource
spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/serendipity
spring.datasource.username=admin
spring.datasource.password=secret

# Spring JPA
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.naming.physical-strategy=com.vladmihalcea.hibernate.type.util.CamelCaseToSnakeCaseNamingStrategy
3

HI Robinyo - please find below details

spring:
datasource:
driver-class-name: org.mariadb.jdbc.Driver
username: user
password: password
url: jdbc:mariadb://localhost:3306/flowable
hikari:
minimum-idle: 5
idle-timeout: 300000

regards
sameer

4

Hey @sameer.uppal,

There is no single configuration that would work for everyone. It all depends on how much requests are hitting your application.

Our Example UI applications are configured with this:

# 10 minutes
spring.datasource.hikari.maxLifetime=600000
# 5 minutes
spring.datasource.hikari.idleTimeout=300000
spring.datasource.hikari.minimumIdle=10
spring.datasource.hikari.maximumPoolSize=50