I am building a RESTful interface to a MariaDB-hosted database, and I cannot figure out how to properly configure HikariCP so that my database connections don't time out after the server has been idle for a while.
I am on Linux, Java 1.8, and my database server is stock MariaDB 5.5.60. My application uses the following tech stack:
- spring-boot-starter-jdbc:2.0.1
- spring-boot-data-rest:2.0.1
- jdbi3-core:3.1.0
- jdbi3-sqlobject:3.1.0
- mysql-connector-java:5.1.46
- HikariCP:2.7.8 (implicitly provided via Spring)
My application.properties file currently looks like this:
spring.datasource.url=jdbc:mysql://localhost/my_database
spring.datasource.username=myusername
spring.datasource.password=myp@ssw0rd
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
# 15 min * 60 sec * 1000 ms = 900000
spring.datasource.hikari.maxLifetime=900000
The "maxLifetime" value is being ignored. I have tried all sorts of Hikari-related things in this file (many found here on SO) but none of them seem to work. When I try hitting the server after it has been idle overnight, I get the following warning:
com.zaxxer.hikari.pool.ProxyConnection: HikariPool-1 - Connection com.mysql.jdbc.JDBC4Connection@140ae1bb marked as broken because of SQLSTATE(08S01) ,ErrorCode(0)
com.mysql.jdbc.exception.jdb4.CommunicationsException: The last packet successfully received from the server was 422,968,077 milliseconds ago. The last packet sent successfully to the server was 422,968,086 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
...and then a pile of errors and stack traces from which I'll spare you.
My intuition tells me that there is some magical combo of parameters missing from my application.properties file, but I'm at a loss. I am also at a loss with how to verify it's actually working without having to wait overnight.
Any help is appreciated!
