How to Prevent Spring Applications from Losing Connection to MySQL After 8 Hours?

What configurations are needed to ensure that a Spring application maintains a persistent connection to a MySQL database without dropping after 8 hours?

<property name="spring.datasource.validation-query" value="SELECT 1"/>

Maintaining a persistent connection between a Spring application and a MySQL database is crucial for applications that rely on continuous database interactions. A common issue is the loss of connection after a certain idle period, often due to server side timeouts. This guide explains how to configure your Spring application to avoid this issue effectively.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/mydb"/>
    <property name="username" value="user"/>
    <property name="password" value="password"/>
    <property name="validationQuery" value="SELECT 1"/>
    <property name="testOnBorrow" value="true"/>
    <property name="testWhileIdle" value="true"/>
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
</bean>

Causes

• MySQL server timeout settings
• Spring datasource configurations
• Network instability
• Connection pooling timeout settings

Solutions

• Configure the connection pool to test connections before use, using a validation query.
• Set the `validation-query` property in your Spring configuration to a simple query (e.g., SELECT 1).
• Adjust MySQL server `wait_timeout` and `interactive_timeout` settings to prevent premature disconnections.
• Implement a connection keep-alive mechanism to regularly ping the database.

Mistake: Not setting a validation query for the connection pool.

Solution: Ensure you set `validation-query` to a lightweight SQL command, such as `SELECT 1`.

Mistake: Neglecting to adjust server timeout settings.

Solution: Modify the MySQL `wait_timeout` and `interactive_timeout` parameters to higher values, such as 28800 seconds (8 hours).