I am new to spring boot so doesn't know much about configuration in application.properties or pom.xml . I'm trying to do a CRUD operation in MySQL DB hosted in Azure VM. Once I start my spring-boot application and perform a DB operation, it works fine the first time. After a while, say 4-5 mins, when another operation is performed, then the springboot application fails to connect to the DB and throws Exception.
PSB the log:
2019-12-23 14:43:00.063 DEBUG 64148 --- [http-nio-4400-exec-5] o.s.web.servlet.DispatcherServlet : GET "/rest/path/getFrequentDetail?queryParam1=value1&queryParam2=value2", parameters={masked}
2019-12-23 14:43:00.063 DEBUG 64148 --- [http-nio-4400-exec-5] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped to public org.springframework.http.ResponseEntity<com.bla.bla> com.bla.bla(java.lang.String,java.lang.String) throws java.io.IOException
2019-12-23 14:43:00.064 DEBUG 64148 --- [http-nio-4400-exec-5] o.h.e.t.internal.TransactionImpl : On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
2019-12-23 14:43:00.064 DEBUG 64148 --- [http-nio-4400-exec-5] o.h.e.t.internal.TransactionImpl : begin
2019-12-23 14:43:05.073 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@503a164d (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:10.077 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@56e33182 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:15.079 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@43689c2d (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:20.082 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@306934d0 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:25.085 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@7754c08f (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:30.087 WARN 64148 --- [http-nio-4400-exec-5] com.zaxxer.hikari.pool.PoolBase : HikariPool-2 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@3a33fdce (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.
2019-12-23 14:43:30.091 DEBUG 64148 --- [http-nio-4400-exec-5] o.h.engine.jdbc.spi.SqlExceptionHelper : Unable to acquire JDBC Connection [n/a]
java.sql.SQLTransientConnectionException: HikariPool-2 - Connection is not available, request timed out after 30024ms.
at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
application.properties
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# Hikari parameters
spring.datasource.hikari.connectionTimeout=30000
spring.datasource.hikari.idleTimeout=600000
spring.datasource.hikari.maxLifetime=1800000
spring.datasource.initialize=true
spring.datasource.schema=classpath:schema.sql
spring.batch.initialize-schema=ALWAYS
spring.batch.job.enabled: false
I've searched through the internet for similar issues and mostly it was related to connection remaining open or MySQL timeout configuration. Both of these seems fine in my case.
All operations are done using JPA. Timeout in mysql is fairly high:
- innodb_lock_wait_timeout 50
- wait_timeout 28800
- mysqlx_wait_timeout 28800
Please help.
