Spring Boot JPA connection validation not working

Question

I'am currently debugging a small application that is build using spring boot(1.1.2.Release). I got a problem with reconnecting to the database if a connection is lost (due to wait_timeout in production, or killed connection in development). I'm currently using the following configuration parameters (application.properties):

spring.datasource.url=jdbc:mysql://localhost:3306/test?autoreconnect=true
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.test-on-borrow=true
spring.datasource.test-while-idle=true
spring.datasource.validation-query=SELECT 1;
spring.datasource.initial-size=2
... username+pw

spring.jpa.generate-ddl=true
spring.jpa.show-sql=true

This results in the following datasource:

org.apache.tomcat.jdbc.pool.DataSource@73e369e5{ConnectionPool[
defaultAutoCommit=null;
defaultReadOnly=null;
defaultTransactionIsolation=-1;
defaultCatalog=null;
driverClassName=com.mysql.jdbc.Driver;
maxActive=100;
maxIdle=100;
minIdle=10;
initialSize=2;
maxWait=30000;
testOnBorrow=true;
testOnReturn=false;
timeBetweenEvictionRunsMillis=5000;
numTestsPerEvictionRun=0;
minEvictableIdleTimeMillis=60000;
testWhileIdle=true;
testOnConnect=false;
password=********;
url=jdbc:mysql://localhost:3306/test?autoreconnect=true;
username=test;
validationQuery=SELECT 1;
;
validationQueryTimeout=-1;
validatorClassName=null;
validationInterval=30000;
accessToUnderlyingConnectionAllowed=true;
removeAbandoned=false;
removeAbandonedTimeout=60;
logAbandoned=false;
connectionProperties=null;
initSQL=null;
jdbcInterceptors=null;
jmxEnabled=true;
fairQueue=true;
useEquals=true;
abandonWhenPercentageFull=0;
maxAge=0;
useLock=false;
dataSource=null;
dataSourceJNDI=null;
suspectTimeout=0;
alternateUsernameAllowed=false;
commitOnReturn=false;
rollbackOnReturn=false;
useDisposableConnectionFacade=true;
logValidationErrors=false;
propagateInterruptState=false;
ignoreExceptionOnPreLoad=false;
}

My Problem is now that when a connection is lost it takes quite a while till the connection is reestablish. In the mean time the users get an empty page and exceptions are thrown on the server side. From my understanding testOnBorrow should test the connection every time before it is used and testWhileIdle every 30 seconds. But this is obiously not the case. When I look at mysql it seems that every 35 seconds something happens and the sleep time resets, but I see no querys in the application log. The validation queries seem to be missing completely.

I access the database through spring data's repositories.

I currently have no further Ideas what to try.

Answer 1

Try swapping to HikariCP instead of tomcat for your connection pool, as it appears to handle timeouts/connection loss a lot better. Spring Boot will autoconfigure a HikariCP if it is present on the classpath and tomcat-jdbc isn't.