Connection to Db dies after >4

by

The easiest way is to specify the autoReconnect property in the JDBC url, although this isn’t the recommended approach.

spring.datasource.url = jdbc:mysql://localhost:3306/test?autoReconnect=true

This can give issues when you have an active connection and during a transaction something happens and a reconnect is going to happen. It will not give issues when the connection is validated at the start of the transaction and a new connection is acquired at the start.

However it is probably better to enable validation of your connections during the lifetime of your application. For this you can specify several properties.

First start by specifying maximum number of connections you allow for the pool. (For a read on determining the max poolsize read this).

spring.datasource.max-active=10

You also might want to specify the number of initial connections

spring.datasource.initial-size=5

Next you want to specify the min and max number of idle connections.

spring.datasource.max-idle=5
spring.datasource.min-idle=1

To validate connection you need to specify a validation-query and when to validate. As you want to validate periodically, instead of when a connection is retrieved from the pool (this to prevent broken connections in your pool).

spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=true
spring.datasource.validation-query=SELECT 1

NOTE: The usage of a validation-query is actually discouraged with as JDBC4 has a better/different way of doing connection validation. HikariCP will automatically call the JDBC validation method when available.

Now that you are also validating while a connection is idle you need to specify how often you want to run this query for the connections and when a connection is considered idle.

spring.datasource.time-between-eviction-runs-millis=5000 (this is the default)
spring.datasource.min-evictable-idle-time-millis=60000 (this is also default)

This all should trigger validation of your (idle) connections and when an exception occurs or the idle period has passed your connections will be removed from the pool.

Assuming you are using Tomcat JDBC as the connection pool this is a nice read of what and how to configure.

UPDATE: Spring Boot 2.x switched the default connection pool to HikariCP instead of Tomcat JDBC.

More Related Contents:

  1. How to use Spring Boot with MySQL database and JPA?
  2. String variable in java to get and store the json array in table column
  3. How to properly use findBySomeOtherId not findById in Spring data jpa?
  4. org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set
  5. What is this spring.jpa.open-in-view=true property in Spring Boot?
  6. Spring Boot, Spring Data JPA with multiple DataSources
  7. Spring Boot – Cannot determine embedded database driver class for database type NONE
  8. Spring Boot + JPA : Column name annotation ignored
  9. How to annotate MYSQL autoincrement field with JPA annotations
  10. How to store Java Date to Mysql datetime with JPA
  11. Unable to get spring boot to automatically create database schema
  12. Hibernate: Automatically creating/updating the db tables based on entity classes
  13. How to resolve Unable to load authentication plugin ‘caching_sha2_password’ issue
  14. No Dialect mapping for JDBC type: 1111
  15. Deserialize Java 8 LocalDateTime with JacksonMapper
  16. @GeneratedValue polymorphic abstract superclass over MySQL
  17. Spring boot JPA insert in TABLE with uppercase name with Hibernate
  18. When are connections returned to the connection pool with Spring JPA (Hibernate) Entity Manager?
  19. Hibernate: Create Mysql InnoDB tables instead of MyISAM
  20. Spring-boot: required a bean named ‘entityManagerFactory’ that could not be found
  21. Table ‘DBNAME.hibernate_sequence’ doesn’t exist
  22. Why has javax.persistence-api been replaced by jakarta.persistence-api in spring data jpa starter?
  23. How to configure port for a Spring Boot application
  24. Hibernate JPA Sequence (non-Id)
  25. Can we connect remote MySQL database in Android using JDBC? [closed]
  26. right syntax to use near ‘?’
  27. CORS allowed-origin restrictions aren’t causing the server to reject requests
  28. How to exclude *AutoConfiguration classes in Spring Boot JUnit tests?
  29. Encoded password does not look like BCrypt
  30. org.hibernate.loader.MultipleBagFetchException: cannot simultaneously fetch multiple bags

Leave a Comment