HikariCP is loosing connection to MySQL after being idle for a while

Question

I'm using HIkariCP, Hibernate and MySQL. The problem is: When application starts, everything works fine. But when I leave it idle for some time and then get back and try to get connection from Hikari, it throws exception:

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 database is located in external server and it has some timeouts/session expiration features and I cannot change it. I suppose the problem is caused exactly by some kind of session expiration due to too big inactivity time (because problem happens only after application remains idle for some time). It may by similar problem, I don't know what is the exact reason why connection is lost.

And the question is: how to force Hikari to refresh the connection in such situation instead of trowing exceptions? (or where else the problem may be?)

My config:

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

score 1 · Answer 1 · answered Jul 27 '17 at 12:55

Right from the docs. You will need to figure out what the external servers timeouts are and configure HikariCP's to be a little shorter.

⌚maxLifetime This property controls the maximum lifetime of a connection in the pool. An in-use connection will never be retired, only when it is closed will it then be removed. We strongly recommend setting this value, and it should be at least 30 seconds less than any database or infrastructure imposed connection time limit. A value of 0 indicates no maximum lifetime (infinite lifetime), subject of course to the idleTimeout setting. Default: 1800000 (30 minutes)

HikariCP is loosing connection to MySQL after being idle for a while

1 Answers1