Hello,
We all have a problem with disconnected databases that at some point recover.
To recover we restart the database and 'use':
spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1
or
spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.validationQuery=SELECT 1
or
similar...
But then we get to hear from everywhere that we cannot/should not do that, because (for example): https://stackoverflow.com/questions/22684807/spring-boot-jpa-configuring-auto-reconnect https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html
...okay, what are we suppose to do when a database get targeted and downed? We bring the database up again and then what..? There are no good solutions around the web of how to handle this. Can you, the spring (boot) team, with Apache, Oracle/mysql, mongo db, etc..., come together and find a solution of this problem, please? Would like to have a good solution for a problem that does not seem that obvious.
Comment From: wilkinsona
Both of those configuration snippets look fine to me. Neither is using the not-recommended autoReconnect attribute in the MySQL JDBC URL. If you are using HikariCP (Spring Boot’s default connection pool), connections should be reestablished automatically without you having to provide any configuration.
If you have specific examples of connections to a data store not being reestablished when you believe that they should be, we can take a look and see if Spring Boot’s default configuration for that data store is the cause.
Comment From: Adriansun
Thank you for the answer.
Overall I do not think people seem to understand that the autoReconnect should not be used, but what I wrote above should be used. Maybe this should be emphasized in the documentation that what I wrote above is not just an temporary / okay solution until it has been fixed, but is actually the fix.
There is also a bit of information missing in the Spring Boot Reference Guide (1) where it says that if nothing else has been said then Spring defaults to HikariCP. Through HikariCp Spring obtains sane default values, but does not say if, for example, testOnBorrow and validationQuery are set. Are they set by default or do we have to insert into our prop files: spring.datasource.testOnBorrow=true and spring.datasource.validationQuery=SELECT 1 ?
1) https://docs.spring.io/spring-boot/docs/2.0.4.RELEASE/reference/htmlsingle/#boot-features-connect-to-production-database
Comment From: snicoll
Overall I do not think people seem to understand that the autoReconnect should not be used
I don't see anything concrete that is backing that statement. I don't think Spring Boot should explain a MySQL behaviour. This also has nothing to do with how the connection pool operates and such property has to be set on the jdbc URL, which Spring Boot does not interfere with.
The same goes for the connection pool. We auto-configure one and expose available properties to be used by the user. Each situation is different and if a sane default is to be available for most users, then it would make sense to request the connection pool to apply it. If you feel that's not the case or some documentation is missing, please raise an issue with the HikariCP project.