How to Fix MySQL 500 Errors by Tuning wait_timeout and autoReconnect

Problem Overview

After deploying a project, the site works initially but returns a 500 Server Error the next day. Logs show MySQL exceptions: the last packet was received after a period longer than the wait_timeout setting.

The log suggests checking the database connection validity before operations or enabling the autoReconnect property.

Root Cause

MySQL’s default wait_timeout is 28800 seconds (8 hours). If a connection remains idle longer, MySQL automatically closes it. The error occurred after an overnight idle period, causing the connection to be dropped.

Running SHOW PROCESSLIST shows connections in the Sleep state and how long they have been idle.

Solutions

1. Enable autoReconnect

Add the JDBC URL parameter autoReconnect=true so the driver automatically re‑establishes a closed connection.

jdbc:mysql://127.0.0.1:3306/stock_tweet?autoReconnect=true

MySQL documentation warns that autoReconnect has side effects:

Transactions on the original connection are rolled back.

Table locks held by the original connection are released.

The session is lost and a new session is created.

User‑defined variables disappear.

Prepared statements are lost.

Performance metrics for the connection are reset.

2. Increase wait_timeout / interactive_timeout

Adjust the server variables to a larger value (e.g., 2 880 000 seconds) either for the current session or permanently in my.cnf.

mysql> set global wait_timeout=2880000;
mysql> set global interactive_timeout=2880000;

Or edit /etc/my.cnf under the [mysqld] section:

[mysqld]
wait_timeout=2880000
interactive_timeout=2880000

Setting the timeout too high can cause many idle connections, consuming memory and eventually hitting the “too many connections” limit.

3. Tune Connection Pool (HikariCP)

When using Spring Boot 2.x with the default HikariCP pool, configure the following parameters:

maximum-pool-size : maximum concurrent connections (default 10). A practical formula is connections = (core_count * 2) + effective_spindle_count. For a 4‑core, 1‑disk server, this yields 9 → round up to 10.

minimum-idle : minimum number of idle connections kept alive.

max-lifetime : maximum lifetime of a connection in the pool; should be less than MySQL’s wait_timeout (e.g., a few hours).

idle-timeout : time a connection can stay idle before being closed.

Reference: HikariCP pool sizing wiki (https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing).

# core_count: number of CPUs
# effective_spindle_count: number of disks
connections = ((core_count * 2) + effective_spindle_count)

Proper tuning prevents unexpected disconnections and improves backend stability.