Resolving Database Connection Loss After 424 Hours in Spring Boot with Hibernate

An issue arises in a Spring Boot application using JPA-Hibernate with MySQL when the connection to the database is lost after a duration of 424 hours. The error log displays:

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 56,006,037 milliseconds ago.  The last packet sent successfully to the server was 56,006,037 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.

To address this issue, it is recommended to configure appropriate connection properties in the application's configuration file (e.g., application.properties):

# Connection validation and pool configuration
spring.datasource.max-active=10
spring.datasource.initial-size=5
spring.datasource.max-idle=5
spring.datasource.min-idle=1

# Periodic connection validation
spring.datasource.test-while-idle=true
spring.datasource.validation-query=SELECT 1

# Idle connection management
spring.datasource.time-between-eviction-runs-millis=5000
spring.datasource.min-evictable-idle-time-millis=60000

These settings enable the connection pool to:

• Establish a maximum number of active connections (spring.datasource.max-active).
• Validate connections periodically (spring.datasource.test-while-idle).
• Remove idle connections if they have not been used within a specified time (spring.datasource.min-evictable-idle-time-millis).

By implementing these configurations, the connection pool will regularly test the validity of connections and replace those that have become stale, ensuring stable database connectivity even after extended periods of inactivity.