Hikaricp: Failed to validate connection org.mariadb.jdbc.MariaDbConnection (Connection.setNetworkTimeout cannot be called on a closed connection).

[duplxey]

Environment

HikariCP version: 3.3.1
JDK version     : 1.8.0_111
Database        : 5.5.60-MariaDB
Driver version  : 2.4.2 (org.mariadb.jdbc.mariadb-java-client)

---

Problem

HikariCP & database connection works well as long as it doesn't idle for more than ~10 minutes. If you try to execute a query after 10 minutes of idling the following warnings & errors will be thrown:

[JDA MainWS-ReadThread] WARN com.zaxxer.hikari.pool.PoolBase - HikariPool-1 - Failed to validate connection org.mariadb.jdbc.MariaDbConnection@2b6d3853 (Connection.setNetworkTimeout cannot be called on a closed connection). Possibly consider using a shorter maxLifetime value.
java.sql.SQLTransientConnectionException: HikariPool-1 - Connection is not available, request timed out after 30025ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:697)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:196)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:161)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100)
    at net.cubeverse.cvdiscordbot.database.Database.getConnection(Database.java:45)
    at net.cubeverse.cvdiscordbot.database.DiscordVerifyDAO.isVerified(DiscordVerifyDAO.java:81)
    at net.cubeverse.cvdiscordbot.command.discord.VerifyCommand.execute(VerifyCommand.java:25)
    at net.cubeverse.cvdiscordbot.core.command.manager.CommandManager.executeDiscordCommand(CommandManager.java:34)
    at net.cubeverse.cvdiscordbot.listeners.MessageListeners.onPrivateMessageReceived(MessageListeners.java:21)
    at net.dv8tion.jda.core.hooks.ListenerAdapter.onEvent(ListenerAdapter.java:397)
    at net.dv8tion.jda.core.hooks.InterfacedEventManager.handle(InterfacedEventManager.java:84)
    at net.dv8tion.jda.core.handle.MessageCreateHandler.handleInternally(MessageCreateHandler.java:111)
    at net.dv8tion.jda.core.handle.SocketHandler.handle(SocketHandler.java:37)
    at net.dv8tion.jda.core.requests.WebSocketClient.onDispatch(WebSocketClient.java:868)
    at net.dv8tion.jda.core.requests.WebSocketClient.onEvent(WebSocketClient.java:766)
    at net.dv8tion.jda.core.requests.WebSocketClient.handleEvent(WebSocketClient.java:745)
    at net.dv8tion.jda.core.requests.WebSocketClient.onBinaryMessage(WebSocketClient.java:903)
    at com.neovisionaries.ws.client.ListenerManager.callOnBinaryMessage(ListenerManager.java:368)
    at com.neovisionaries.ws.client.ReadingThread.callOnBinaryMessage(ReadingThread.java:270)
    at com.neovisionaries.ws.client.ReadingThread.handleBinaryFrame(ReadingThread.java:990)
    at com.neovisionaries.ws.client.ReadingThread.handleFrame(ReadingThread.java:749)
    at com.neovisionaries.ws.client.ReadingThread.main(ReadingThread.java:108)
    at com.neovisionaries.ws.client.ReadingThread.runMain(ReadingThread.java:64)
    at com.neovisionaries.ws.client.WebSocketThread.run(WebSocketThread.java:45)
Caused by: java.sql.SQLException: Connection.setNetworkTimeout cannot be called on a closed connection
    at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getSqlException(ExceptionMapper.java:277)
    at org.mariadb.jdbc.MariaDbConnection.setNetworkTimeout(MariaDbConnection.java:1700)
    at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:552)
    at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:168)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:185)
    ... 22 more

Establishing the connection

I establish the connection with the following code and return it via #getConnection(). I've tried experimenting around with maxLifetime, but I couldn't solve the issue. I've also checked MySQL timeout settings to make sure maxLifetime connection is shorter than wait_timeout.

MariaDB [(none)]> SHOW VARIABLES LIKE '%timeout';
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| connect_timeout            | 10       |
| delayed_insert_timeout     | 300      |
| innodb_lock_wait_timeout   | 50       |
| innodb_rollback_on_timeout | OFF      |
| interactive_timeout        | 28800    |
| lock_wait_timeout          | 31536000 |
| net_read_timeout           | 30       |
| net_write_timeout          | 60       |
| slave_net_timeout          | 3600     |
| thread_pool_idle_timeout   | 60       |
| wait_timeout               | 28800    |
+----------------------------+----------+

public class Database {

    private final HikariDataSource hikari;

    public Database() {
        // Setting up the hikari config
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl("jdbc:mysql://ip:port/database");
        hikariConfig.setUsername("xxxxx");
        hikariConfig.setPassword("xxxxx");
        hikariConfig.addDataSourceProperty("cachePrepStmts", "true");
        hikariConfig.addDataSourceProperty("prepStmtCacheSize", "250");
        hikariConfig.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        hikariConfig.addDataSourceProperty("useServerPrepStmts", "true");
        hikariConfig.addDataSourceProperty("useLocalSessionState", "true");
        hikariConfig.addDataSourceProperty("rewriteBatchedStatements", "true");
        hikariConfig.addDataSourceProperty("cacheResultSetMetadata", "true");
        hikariConfig.addDataSourceProperty("cacheServerConfiguration", "true");
        hikariConfig.addDataSourceProperty("elideSetAutoCommits", "true");
        hikariConfig.addDataSourceProperty("maintainTimeStats", "false");
        hikariConfig.addDataSourceProperty("maxLifetime", "600000");
        // Source: https://github.com/brettwooldridge/HikariCP/wiki/MySQL-Configuration

        // Initialize a new hikari object
        hikari = new HikariDataSource(hikariConfig);
    }

    public Connection getConnection() {
        try {
            System.out.println(S.INFO.getPrefix() + "Successfully established the database connection.");
            return hikari.getConnection();
        } catch (SQLException e) {
            System.out.println(S.ERROR.getPrefix() + "Failed to establish the database connection.");
            e.printStackTrace();
        }
        return null;
    }
}

[Skymirrh]

Hi, similar problem here. Configuration:

HikariCP version: 2.7.9 (Springboot 2.0.5)
JDK version     : 1.8.0_202
Database        : MariaDB 10.2.23
Driver version  : org.mariadb.jdbc:mariadb-java-client:2.2.6

Connection is established through Spring as described in https://github.com/brettwooldridge/HikariCP/wiki/Spring-Hibernate-with-Annotations

MariaDB's wait_timeout is set to 31 minutes while HikariCP's maxLifetime is set to 30 minutes.

Initialization and usage works fine at first (pool of 10 connections). After leaving the connections idle for some time (in our testing, somewhere between 10 and 20 minutes), the same behavior will start to happen (Connection.setNetworkTimeout cannot be called on a closed connection).

This happens both on Azure-deployed MariaDB instances and local MariaDB instances. At first we thought this was caused by network agents in between Spring and MariaDB (such as Tomcat or nginx), but it does not seem to be the case (happens in various network configurations, independent of Tomcat/nginx stack).

Lowering maxLifetime below 10 minutes seems to work fine, but is a bit... unaesthetic... considering it was intended to be 30 minutes.

[duplxey]

Lowering maxLifetime below 10 minutes seems to work fine, but is a bit... unaesthetic... considering it was intended to be 30 minutes.

I can confirm that lowering the maxLifetime to about 5 minutes works fine.