MariaDB, HikariCP and connection lifetime in SpringBoot

Posted by

In some cases, when using a Hikari connection pool and a MySQL or MariaDB database, you may see the following error in the logs:

WARN 1 --- [_ClusterManager] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection com.mysql.cj.jdbc.ConnectionImpl@7a8c7da4 (No operations allowed after connection closed.). Possibly consider using a shorter maxLifetime value.

In most cases, this means that the value for maxLifetime of Hikari connection is configured to be higher than the wait_timeout of MySQL/MariaDB. So it happens that Hikari tries to use the connection which was already closed by the database server.

wait_timeout is the “number of seconds the server waits for activity on a noninteractive connection before closing it”.

maxLifetime “property controls the maximum lifetime of a connection in the pool … and it should be several seconds shorter than any database or infrastructure imposed connection time limit”. The unit is milliseconds.

To check the current value set for wait_timeout:

MariaDB [(none)]> show global variables like "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 600   |
+---------------+-------+

This means that the global value for wait_timeout is 600 seconds. So you need to set the value for Hikari maxLifetime to be several seconds less.

For example, if you are using Spring Boot, set the following in the application.properties file:

spring.datasource.hikari.max-lifetime = 580000

If you configured Hikari’s minimumIdle to be less than maximumPoolSize, then also adjust the value for idleTimeout accordingly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.