Problem with losing database connection via Spring Boot (Postgres)

After some time of running Camunda in Spring Boot the connections get lost and Camunda Spring Boot silently fails. Queries are starting to return null instead of saying that there is a Persistance error.

For example:
identity.createUserQuery().userId("demo").singleResult();

will return null when user exists.

I have managed to fix problem by adding:

spring.datasource.tomcat.validation-query=SELECT 1
spring.datasource.tomcat.validation-interval=30000
spring.datasource.tomcat.test-on-borrow=true
spring.datasource.tomcat.remove-abandoned=true
spring.datasource.tomcat.remove-abandoned-timeout=55
spring.datasource.tomcat.max-active=100
spring.datasource.tomcat.test-while-idle=true

to my application.properties.

But still when application is not used for a while request made by api are not getting executed. Request is stuck in status Pending. Every request after the first one is then ok.

Did anyone had simular issue and managed to solve it ?

UPDATE:
Still with this configuration identityService is sometimes returning me null for user that exists in database.

Here is the log i got:

Closing JDBC Connection [Transaction-aware proxy for target Connection [jdbc:postgresql://db_bpm:5432/camunda?autoReconnect=true, UserName=camunda, PostgreSQL Native Driver]]
Retrieved value [org.springframework.jdbc.datasource.ConnectionHolder@7df7d884] for key [org.apache.commons.dbcp.BasicDataSource@69c1237b] bound to thread [http-nio-8080-exec-2]
Triggering beforeCommit synchronization
Triggering beforeCompletion synchronization
Removed value [org.springframework.jdbc.datasource.ConnectionHolder@47230571] for key [org.apache.commons.dbcp.BasicDataSource@69c1237b] from thread [http-nio-8080-exec-8]
ENGINE-13006 Finishing command -------------------- DbUserQueryImpl ----------------------
User with user id demo doesn't exist in BPM subsystem.

The message: “User with user id demo doesn’t exist in BPM subsystem” is thrown when UserQuery returns null.

It seems that query is not executed at all since i have slf4j enabled for MyBatis and i don’t see executed query which returns 0 result.

The strange thing is that this error is gone when i enter the Camunda Admin and go to List users page. After that everything works :smiley: