HikariDataSource closes automatically when I query from it

Question

I have configured two database in my spring boot application as master and slave db. Whenever the application queries anything from the slave db, hikari pool is automatically closed and its throws error as

o.h.engine.jdbc.spi.SqlExceptionHelper  (137)    |> SQL Error: 0, SQLState: null
[ http-nio-2100-exec-6 ] |ERROR|    o.h.engine.jdbc.spi.SqlExceptionHelper  (142)    |> HikariDataSource HikariDataSource (REPORT_POOL) has been closed.
 [ http-nio-2100-exec-6 ] |ERROR|    c.n.c.exception.GlobalExceptionHandler  (485)   |> Critical Error: 
org.springframework.orm.jpa.JpaSystemException: Unable to acquire JDBC Connection; nested exception is org.hibernate.exception.GenericJDBCException: Unable to acquire JDBC Connection

my configurations for db is as follows:

#Master Database
master.datasource.hikari.url=jdbc:postgresql://localhost:5432/master_db
master.datasource.hikari.username=*****
master.datasource.hikari.password=*****
master.datasource.hikari.minimum-idle=10
master.datasource.hikari.maximum-pool-size=30
master.datasource.hikari.connection-timeout=30000
master.datasource.hikari.leakDetection=15000
master.datasource.hikari.idle-timeout=25000
master.datasource.hikari.max-lifetime=600000
master.datasource.hikari.auto-commit=true

#Slave Database Read
report.datasource.hikari.url=jdbc:postgresql://localhost:5432/slave_db
report.datasource.hikari.username=*****
report.datasource.hikari.password=*****
report.datasource.hikari.minimum-idle=10
report.datasource.hikari.maximum-pool-size=30
report.datasource.hikari.connection-timeout=30000
report.datasource.hikari.leakDetection=15000
report.datasource.hikari.idle-timeout=20000
report.datasource.hikari.max-lifetime=600000
report.datasource.hikari.auto-commit=true

I have configured two data sources as

@Bean
@Primary
@Autowired
public DataSource dataSource() {
    DataSourceRouting routingDataSource = new DataSourceRouting();
    routingDataSource.initDatasource(masterDataSource(), readDataSource());
        return routingDataSource;
    }

    private DataSource masterDataSource() {
        HikariConfig master = new HikariConfig();
        master.setPoolName("MASTER_POOL");
        master.setJdbcUrl(env.getProperty(String.format("%s.url", MASTER)));
        master.setUsername((env.getProperty(String.format("%s.username", MASTER))));
        master.setPassword((env.getProperty(String.format("%s.password", MASTER))));
        master.setMinimumIdle(Integer.valueOf(env.getProperty(String.format("%s.minimum-idle", MASTER))));
        master.setMaximumPoolSize(Integer.valueOf(env.getProperty(String.format("%s.maximum-pool-size", MASTER))));
        master.setConnectionTimeout(Long.valueOf(env.getProperty(String.format("%s.connection-timeout", MASTER))));
        master.setIdleTimeout(Long.valueOf(env.getProperty(String.format("%s.idle-timeout", MASTER))));
        master.setMaxLifetime(Long.valueOf(env.getProperty(String.format("%s.max-lifetime", MASTER))));
        master.setLeakDetectionThreshold(Long.valueOf(env.getProperty(String.format("%s.leakDetection", MASTER))));
        master.setAutoCommit(Boolean.valueOf(env.getProperty(String.format("%s.auto-commit", MASTER))));
        masterDataSource = new HikariDataSource(master);
        return masterDataSource;
    }
    
    private DataSource readDataSource() {
        HikariConfig report = new HikariConfig();
        report.setPoolName("REPORT_POOL");
        report.setJdbcUrl(env.getProperty(String.format("%s.url", REPORT)));
        report.setUsername((env.getProperty(String.format("%s.username", REPORT))));
        report.setPassword((env.getProperty(String.format("%s.password", REPORT))));
        report.setMinimumIdle(Integer.valueOf(env.getProperty(String.format("%s.minimum-idle", REPORT))));
        report.setMaximumPoolSize(Integer.valueOf(env.getProperty(String.format("%s.maximum-pool-size", REPORT))));
        report.setConnectionTimeout(Long.valueOf(env.getProperty(String.format("%s.connection-timeout", REPORT))));
        report.setIdleTimeout(Long.valueOf(env.getProperty(String.format("%s.idle-timeout", REPORT))));
        report.setMaxLifetime(Long.valueOf(env.getProperty(String.format("%s.max-lifetime", REPORT))));
        report.setLeakDetectionThreshold(Long.valueOf(env.getProperty(String.format("%s.leakDetection", REPORT))));
        report.setAutoCommit(Boolean.valueOf(env.getProperty(String.format("%s.auto-commit", REPORT))));
        report.setReadOnly(Boolean.valueOf(env.getProperty(String.format("%s.read-only", REPORT))));

        try (HikariDataSource readDataSource = new HikariDataSource(report)) {
            readDataSource.getConnection();
            return readDataSource;
        } catch (Exception e) {
            log.warn("\n\n******REPORT DB NOT FOUND, CONNECTED TO MASTER DB *******\n\n");
            return masterDataSource;
        }
    }      

I have a DataSourceInterceptor for directing request to report db

@Slf4j
@Component
public class DataSourceInterceptor extends HandlerInterceptorAdapter {

    protected static final String[] PREFIX_REPORT_DS = new String[] { "/admin/report/**", "/report/**" };

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler)
            throws Exception {

        String uri = request.getRequestURI();

        if (StringUtil.isStartWith(uri, StringUtil.trimStricks(PREFIX_REPORT_DS))) {
            log.info("Redirect to Report Database for URL: {}", uri);
            DataSourceRouting.setReportRoute();
        }

        return true;

    }
}

And a DataSourceRouting for setting route to slave db

@Slf4j
public class DataSourceRouting extends AbstractRoutingDataSource {

    private static final ThreadLocal<Route> routeContext = new ThreadLocal<>();

    public enum Route {
        MASTER, REPORT
    }


    public static void setReportRoute() {
        routeContext.set(Route.REPORT);
    }

    @Override
    protected Object determineCurrentLookupKey() {
        return routeContext.get();
    }

    public void initDatasource(DataSource masterDs, DataSource reportDs) {
        log.info("Datasource routing...");
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put(Route.MASTER, masterDs);
        dataSourceMap.put(Route.REPORT, reportDs);
        this.setTargetDataSources(dataSourceMap);
        this.setDefaultTargetDataSource(masterDs);

    }
}

When the slave db is down or cannot be connnected during the application start-up, it gets connected to master db, then there is no issue in the application but when the slave db is connected, and the application tries to query from it, the above exception is thrown

Answer 1

The problem is, as hinted already in the comments, your configuration. Whilst being overly extensive it is also wrong. What is wrong you are using a try-with-resources which will automatically close the resource after the block finishes. So in the end you are always ending up with a closed datasource. Regardless of the fact if it is or isn't connected.

I would strongly recommend following the Spring Boot preferred way of configuring your datasources.

@Configuration
public DatasourceConfiguration {

  @Bean
  @Primary
  public DataSource dataSource() {
    DataSourceRouting routingDataSource = new DataSourceRouting();
    routingDataSource.initDatasource(masterDataSource(), readDataSource());
    return routingDataSource;
  }

  @Bean
  @Primary
  @ConfigurationProperties("master.datasource")
  public DataSourceProperties masterDataSourceProperties() {
    return new DataSourceProperties();
  }

  @Bean
  @ConfigurationProperties("report.datasource")
  public DataSourceProperties reportDataSourceProperties() {
    return new DataSourceProperties();
  }
  
  @Bean
  @ConfigurationProperties("master.datasource.hikari")
  public HikariDataSource masterDataSource(DataSourceProperties masterDataSourceProperties) {
    return masterDataSourceProperties.initializeDataSourceBuilder()
      .type(HikariDataSource.class).build();  
  }

  @Bean
  @ConfigurationProperties("report.datasource.hikari")
  public HikariDataSource masterDataSource(DataSourceProperties reportDataSourceProperties) {
    return reportDataSourceProperties.initializeDataSourceBuilder()
      .type(HikariDataSource.class).build();  
  }
}

The url, username and password property should be set on the datasource directly to prevent start failures.

#Master Database
master.datasource.url=jdbc:postgresql://localhost:5432/master_db
master.datasource.username=*****
master.datasource.password=*****
master.datasource.hikari.minimum-idle=10
master.datasource.hikari.maximum-pool-size=30
master.datasource.hikari.connection-timeout=30000
master.datasource.hikari.leakDetection=15000
master.datasource.hikari.idle-timeout=25000
master.datasource.hikari.max-lifetime=600000
master.datasource.hikari.auto-commit=true

#Slave Database Read
report.datasource.url=jdbc:postgresql://localhost:5432/slave_db
report.datasource.username=*****
report.datasource.password=*****
report.datasource.hikari.minimum-idle=10
report.datasource.hikari.maximum-pool-size=30
report.datasource.hikari.connection-timeout=30000
report.datasource.hikari.leakDetection=15000
report.datasource.hikari.idle-timeout=20000
report.datasource.hikari.max-lifetime=600000
report.datasource.hikari.auto-commit=true
report.datasource.hikari.initializationFailTimeout=-1

To handle breaking connections make sure the min/max are different and that you set a reasonable validationTimeout for the validation. For the report database you could add the initializationFailTimeout and set a negative value. This will not lead to acquiring connections at startup but later on. This will allow you to even start with a broken reporting database, for which the connections, due to validation, later will be available.

Another thing that is wrong in your setup is that yo never reset the routing key, as it is stored in a ThreadLocal it will get reused eventually, as request handling threads are in a pool. You need to clear in the afterCompletion method of the HandlerInterceptor.