1

I have a Spring Boot application which uses Hibernate, and HikariDataSource / HikariPool to talk to the database.

A special feature in the app triggers database restart. Currently this breaks the connections in HikariPool:

Caused by: org.postgresql.util.PSQLException: ERROR: relation "relation_which_really_exists" does not exist
Position: 113
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)

Old version of the app does call programmatically org.hibernate.SessionFactory.close(); which causes restart of HikariDataSource / HikariCP:

2020-08-17T11:36:42.628Z [qtp1340328248-76] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
2020-08-17T11:36:42.698Z [qtp1340328248-76] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.
2020-08-17T11:36:51.266Z [qtp1340328248-12] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Starting...
2020-08-17T11:36:51.515Z [qtp1340328248-12] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-2 - Start completed.

I would like to do the same, but how can I programmatically restart the connection pool in my app? I've seen things like Spring Boot - Handle to Hibernate SessionFactory to get a handle to sessionFactory, and maybe something similar to get a handle to DataSource or CP....but is it OK to close / restart those objects violently, is the Spring Boot context designer to handle such action properly?

There's probably some HikariCP configuration parameters that I can start to experiment with to try reach the same end result, but replicating the old implementation tempts as the easiest / most figured out and most probable to reach same end result.

Janne Mattila
  • 598
  • 7
  • 20

4 Answers4

7

After some study, found out that you can get handle of HikariCP and trigger connection eviction by:

    HikariDataSource hikariDs = (HikariDataSource) dataSource;
    HikariPoolMXBean poolBean = hikariDs.getHikariPoolMXBean();
    poolBean.softEvictConnections();
Janne Mattila
  • 598
  • 7
  • 20
  • Impressive! It already helps, but could you post some additional references as a (study) starting point? I'd like to understand better how to do additional manipulations – Marco Sep 12 '22 at 07:56
  • My memory fails me, I have no idea what my "some study" was back then....I did find this https://github.com/brettwooldridge/HikariCP/wiki/MBean-(JMX)-Monitoring-and-Management now – Janne Mattila Sep 14 '22 at 05:48
1

There is a parameter in HikariCP which is connectionTestQuery This is the query that will be executed just before a connection is given to you from the pool to validate that the connection to the database is still alive. So I think you can use it to check if the connection is still alive or not and then force things. But they mention this in the documentation "If your driver supports JDBC4 we strongly recommend not setting this property. This is for "legacy" drivers that do not support the JDBC4 Connection.isValid() API"

Ennar.ch
  • 659
  • 1
  • 8
  • 26
0

There are following possiblties.

  1. make sure allowPoolSuspension is available in configuration.
  2. make sure hikari version 4 or above.

reset credential:-

HikariDataSource ds = (HikariDataSource) applicationContext.getBean("dataSource");
HikariPoolMXBean hikariPoolMXBean = ds.getHikariPoolMXBean();
if (hikariPoolMXBean != null) {

    hikariPoolMXBean.softEvictConnections();
    
}ds.setUsername(userName);ds.setPassword(password);
Rajesh Rawat
  • 13
  • 4
0

As Rajesh, Janne mentioned, the softEvictConnections() method will close the connections in the pool. This method call will "softly" evict all idle connections from the pool and mark active connections to be evicted as soon as they return to the pool. This means no abrupt termination of ongoing transactions, but all connections will eventually be closed and replaced.

if(datasource instanceof HikariDataSource) {
    ((HikariDataSource) datasource).getHikariPoolMXBean().softEvictConnections();
}
KRISH C
  • 21
  • 4