Resolving MySQL wait_timeout Issues with Druid Connection Pool Configuration

Introduction

Recently, while using MHA for MySQL read/write separation, users occasionally reported a generic "exception" when publishing articles from the backend. Investigation revealed that the problem involved JDBC connection pool parameters and MySQL configuration adjustments.

Problem Review

Exception log (image omitted) highlighted two key issues:

The database connection pool timeout setting exceeds the MySQL wait_timeout value.

The log suggested that adding autoReconnect=true could avoid the exception.

From these points we infer:

The application’s database connection pool timeout parameters are misconfigured.

When installing MySQL, the internal wait_timeout parameter was not tuned for the actual scenario.

Problem Identification

The wait_timeout parameter defines how many seconds the server waits before closing a non‑interactive connection. By default MySQL sets wait_timeout to 28800 seconds (8 hours). If this value is too large, many SLEEP processes accumulate and degrade performance; if it is too small, MySQL may close connections that the application still assumes are valid, leading to the observed exceptions.

In our case, the Druid connection pool had a MaxWait of 60000 ms, which is larger than the MySQL wait_timeout. When a connection stayed idle beyond wait_timeout, MySQL closed it, but Druid still considered it alive, causing the error on the next use.

Solution

We adjusted the MySQL and Druid settings as follows:

Verified that the production MySQL wait_timeout is 28800 seconds.

Reduced Druid’s MaxWait from 60000 ms to 10000 ms, which is less than MySQL’s wait_timeout.

After testing for 8 hours, the error disappeared.

Additional approaches (from the web) include:

Appending &autoReconnect=true to the JDBC URL (effective only for MySQL 4).

Extending MySQL’s idle‑connection recycle time by modifying my.ini (not recommended due to performance impact).

Enabling Druid validation queries and setting setPhyTimeoutMillis to limit physical connection lifetime.

Reference Example

Below is a stable Druid configuration used in the project:

<span>private void configDruidParams(DruidDataSource druidDataSource) {</span>
<span>    druidDataSource.setMaxActive(20);</span>
<span>    druidDataSource.setInitialSize(1);</span>
<span>    // Configure max wait time for obtaining a connection</span>
<span>    druidDataSource.setMaxWait(10000);</span>
<span>    druidDataSource.setMinIdle(1);</span>
<span>    // Interval for eviction runs (ms)</span>
<span>    druidDataSource.setTimeBetweenEvictionRunsMillis(60000);</span>
<span>    // Minimum idle time before eviction (ms)</span>
<span>    druidDataSource.setMinEvictableIdleTimeMillis(30000);</span>
<span>    // Max idle time before eviction (ms)</span>
<span>    druidDataSource.setMaxEvictableIdleTimeMillis(180000);</span>
<span>    // Physical connection max lifetime (ms)</span>
<span>    druidDataSource.setPhyTimeoutMillis(15000);</span>
<span>    druidDataSource.setValidationQuery("select 1");</span>
<span>    druidDataSource.setTestWhileIdle(true);</span>
<span>    druidDataSource.setTestOnBorrow(false);</span>
<span>    druidDataSource.setTestOnReturn(false);</span>
<span>    druidDataSource.setPoolPreparedStatements(true);</span>
<span>    druidDataSource.setMaxOpenPreparedStatements(20);</span>
<span>    druidDataSource.setUseGlobalDataSourceStat(true);</span>
<span>    druidDataSource.setKeepAlive(true);</span>
<span>    druidDataSource.setRemoveAbandoned(true);</span>
<span>    druidDataSource.setRemoveAbandonedTimeout(180);</span>
<span>    try {</span>
<span>        druidDataSource.setFilters("stat,slf4j");</span>
<span>        List filterList = new ArrayList<>();</span>
<span>        filterList.add(wallFilter());</span>
<span>        druidDataSource.setProxyFilters(filterList);
<span>    } catch (SQLException e) {</span>
<span>        e.printStackTrace();</span>
<span>    }</span>
<span>}</span>

For further reading, see the recommended articles linked at the end of the original post.