HikariCP: MariaDB가 최대 5분간 아이돌 상태가 되면 접속 취득을 정지합니다.

HikariCP: MariaDB가 최대 5분간 아이돌 상태가 되면 접속 취득을 정지합니다.

HikariCP(접속 풀링, https://github.com/brettwooldridge/HikariCP))를 사용하여 MariaDB 데이터베이스에 연결하는 간단한 프로그램을 만들었습니다.접속이 5분 이상 아이돌 상태가 되지 않는 한, 모든 것이 정상적으로 동작합니다.5분 동안 아이돌 상태가 되면 모든 쿼리가 실패하고 다음 오류가 발생합니다.

[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

코드(데이터베이스 연결 및 연결 검색)
다음 코드로 연결을 확립하고 #getConnection()을 통해 반환합니다.max Life time으로 실험을 해봤지만 문제를 해결할 수 없었습니다.maxLifetime 연결이 wait_timeout보다 짧은지 확인하기 위해 MySQL 타임아웃 설정도 확인했습니다.

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;
    }
}

타임아웃 설정
기본 타임아웃 설정을 실행하고 있습니다.

+----------------------------+----------+
| 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    |
+----------------------------+----------+

환경

JDK version : 1.8.0_111
Database : 5.5.60-MariaDB
Driver version : 2.4.2 (org.mariadb.jdbc.mariadb-java-client)
OS: : Linux CentOS 7

GitHub의 HikariCP 저장소도 개설했습니다.

언급URL : https://stackoverflow.com/questions/57009796/hikaricp-mariadb-stops-retrieving-connections-after-5-minutes-of-idling