java - HikariCP 在闲置一段时间后失去与 MySQL 的连接

Question

我正在使用 HIkariCP、Hibernate 和 MySQL。问题是:当应用程序启动时，一切正常。但是当我让它闲置一段时间然后返回并尝试从 Hikari 获取连接时，它会抛出异常:

Caused by: java.sql.SQLTransientConnectionException: Main DB Pool - Connection is not available, request timed out after 60072ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:548)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:145)
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:83)
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122)
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35)
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:115)
    ... 59 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 820 887 milliseconds ago.  The last packet sent successfully to the server was 60 060 milliseconds ago.
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:989)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3559)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3459)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3900)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2444)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:157)
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:169)
    ... 64 common frames omitted
Caused by: java.net.SocketTimeoutException: Read timed out
    at java.net.SocketInputStream.socketRead0(Native Method)
    at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
    at java.net.SocketInputStream.read(SocketInputStream.java:170)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at com.mysql.jdbc.util.ReadAheadInputStream.fill(ReadAheadInputStream.java:101)
    at com.mysql.jdbc.util.ReadAheadInputStream.readFromUnderlyingStreamIfNecessary(ReadAheadInputStream.java:144)
    at com.mysql.jdbc.util.ReadAheadInputStream.read(ReadAheadInputStream.java:174)
    at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:3008)
    at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:3469)
    ... 74 common frames omitted

MySQL 数据库位于外部服务器，它有一些超时/ session 过期功能，我无法更改它。我认为这个问题正是由于某种 session 过期引起的，由于不活动时间过长(因为问题仅在应用程序保持空闲一段时间后才会发生)。可能是类似的问题，我不知道连接丢失的确切原因是什么。

问题是:在这种情况下如何强制 Hikari 刷新连接而不是引发异常？ (或者问题可能出在哪里？)

我的配置:

    HikariConfig config = new HikariConfig();

    config.setPoolName("Main DB Pool");
    config.addDataSourceProperty("url", "jdbc:mysql://" + host);

    config.setUsername(user);
    config.setPassword(password);
    config.setDataSourceClassName(dataSourceClass);
    config.setMaximumPoolSize(maxPoolSize);
    config.setIdleTimeout(30000);
    config.setConnectionTimeout(60000);
    config.setValidationTimeout(60000);
    config.setConnectionInitSql("SELECT 1");
    config.setConnectionTestQuery("SELECT 1");
    config.setLeakDetectionThreshold(120000);

    return new HikariDataSource(config);

Answer 1

直接来自文档。您需要弄清楚外部服务器超时是多少，并将 HikariCP 配置得更短一些。

⌚最大生命周期该属性控制池中连接的最长生命周期。正在使用的连接永远不会退休，只有当它关闭时才会被删除。 我们强烈建议设置此值，它应比任何数据库或基础设施施加的连接时间限制至少少 30 秒。值 0 表示没有最大生命周期(无限生命周期)，当然取决于idleTimeout 设置。默认值:1800000(30 分钟)