Resolving MySQL wait_timeout and JDBC Connection‑Pool Exceptions with Druid Configuration

Using MHA for MySQL read/write separation, the author encountered occasional "generic exception" errors when publishing articles, which were traced to improper JDBC connection‑pool parameters and MySQL wait_timeout settings.

Problem review : The logs highlighted two key issues – the database connection‑pool timeout (wait_timeout) being larger than the pool's wait limit, and the possibility of avoiding the exception by enabling autoReconnect=true in the JDBC URL.

Root cause inference : The application’s connection‑pool timeout parameters were mis‑configured, and the MySQL wait_timeout value had not been tuned for the actual workload, leading to either excessive idle SLEEP processes or premature connection loss.

Parameter explanation : wait_timeout defines how long MySQL keeps a non‑interactive connection alive; the default is 28800 seconds (8 hours). Setting it too high creates many idle SLEEP threads, while setting it too low causes connections to be closed while the application still assumes they are valid.

Application‑side Druid configuration (excerpt): druidDataSource.setMaxWait(60000) The author discovered that the Druid MaxWait value of 60000 ms was excessive.

Solution : Reduce the Druid MaxWait to 10000 ms, keep MySQL wait_timeout at 28800 seconds, and verify that the error disappears after the 8‑hour window. The adjustment resolved the exception in the test environment.

Additional approaches (from the web) :

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

Extend MySQL’s idle‑connection recycle time by modifying my.ini (e.g., set to 1 day, though this may degrade performance).

Configure Druid with validation queries and a physical‑connection timeout, e.g., validation‑query, testOnBorrow, testWhileIdle, and set setPhyTimeoutMillis to a reasonable value.

Reference Druid configuration example (stable parameters used in the project):

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