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数据库位于外部服务器中，它有一些超时/会话过期功能，我无法更改。我认为这个问题正是由于太长的非活动时间导致的某种会话过期（因为只有在应用程序空闲一段时间后，问题才会发生）。这可能是由类似的问题，我不知道什么是确切的原因，为什么连接丢失。

问题是：在这种情况下，如何强迫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配置得更短一点。

⌚maxLifetime此属性控制池中连接的最大生存期。正在使用的连接永远不会失效，只有当它关闭时才会被删除。我们强烈建议设置此值，并且该值应至少比任何数据库或基础结构强加的连接时间限制少30秒。值为0表示没有最大生存期（无限生存期），当然取决于idleTimeout设置。默认值：1800000（30分钟）