6

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.

ssindelar
  • 2,833
  • 1
  • 17
  • 36
  • According to this http://stackoverflow.com/questions/667289/why-does-autoreconnect-true-not-seem-to-work it is more of a generic problem. I even doubt it works fine without spring boot as the datasource seems to be configured correctly. – M. Deinum Jun 26 '14 at 05:52
  • Using autoreconnect=true is more a kind of clutching to the last straw in the hope it might help. I know it throws an exception but my understanding is that the connection pool (tomcat-jdbc) due to the validationquery it should be caught and reconnect to the database, even without autoreconnect=true. – ssindelar Jun 26 '14 at 06:34
  • 3
    What you can do instead of validating on borrow you might want to periodically test connections and evict abondoned/idle connections. See http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency – M. Deinum Jun 26 '14 at 06:38
  • Thank you, I think that helped a lot. I will give it a try the next time I get a chance. – ssindelar Jun 26 '14 at 06:51
  • I think you can't put those in application.properties. do you have a org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean bean configured somewhere? put it in datasource bean it's referencing – MarianP Dec 31 '14 at 16:49
  • Note that the use of autoReconnect [is not recommended](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-configuration-properties.html) :
    The use of this feature is not recommended, because it has side effects related to session state and data consistency when applications don't handle SQLExceptions properly, and is only designed to be used when you are unable to configure your application to handle SQLExceptions resulting from dead and stale connections properly.     – Abhishek Ransingh Apr 16 '19 at 18:03

1 Answers1

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.

adam p
  • 1,214
  • 9
  • 18