1

I am having an issue in production in my springboot application which uses HikariPool

Springboot: 2.7.4

Java: 1.8.0_281

Tomcat: 9.0.71

Database: Microsoft SQL Server 2012

application.properties

crm.pool-name=crmPool
crm.maximum-pool-size=600
crm.connection-timeout=30000
crm.max-lifetime=180000
crm.idle-timeout=180000
crm.leak-detection-threshold=180000

Because I have multiple datasources I am creating the connection pool as follows :

public class HikariCPDataSource {

    private static HikariConfig crmConfig = new HikariConfig();
    private static HikariDataSource crmDataSource;

    private static HikariConfig externalDatabaseConfig = new HikariConfig();
    private static HikariDataSource externalDataSource;

    public static void init() {
        initCRMDataSource();
        initexternalDataSource();
    }

    public static void initCRMDataSource() {


        String crmDriverClassName = ConfigUtils.getConfig("spring.datasource.driver-class-name");

        String crmDatabaseServer = ConfigUtils.getConfig("CR_DATABASE_SERVER");
        String crmDatabaseUserName = ConfigUtils.getConfig("CR_DATABASE_USER");
        String crmDatabasePassword = Encryptor.decrypt(ConfigUtils.getConfig("CR_DATABASE_USER_PASSWORD"));
        String crmDatabaseName = ConfigUtils.getConfig("CR_DATABASE_NAME");

        int crmPoolSize = ConfigUtils.getConfigNumeric("crm.maximum-pool-size");
        long crmConnectionTimeout = ConfigUtils.getConfigNumeric("crm.connection-timeout");
        long crmMaxLifeTime = ConfigUtils.getConfigNumeric("crm.max-lifetime");
        long crmIdleTimeout = ConfigUtils.getConfigNumeric("crm.idle-timeout");
        long crmLeakDetectionThreshold = ConfigUtils.getConfigNumeric("crm.leak-detection-threshold");

        String crmPoolName = ConfigUtils.getConfig("crm.pool-name");

        if (crmPoolSize > 0)
            crmConfig.setMaximumPoolSize(crmPoolSize);

        if (crmConnectionTimeout > 0)
            crmConfig.setConnectionTimeout(crmConnectionTimeout);

        if (crmMaxLifeTime > 0)
            crmConfig.setMaxLifetime(crmMaxLifeTime);

        if (crmIdleTimeout > 0)
            crmConfig.setIdleTimeout(crmIdleTimeout);

        if (crmLeakDetectionThreshold > 0)
            crmConfig.setLeakDetectionThreshold(crmLeakDetectionThreshold);

        crmConfig.setDriverClassName(crmDriverClassName);

        String crmJdbcUrl = "jdbc:sqlserver://" + crmDatabaseServer + ";databaseName=" + crmDatabaseName
                + ";encrypt=false";
        crmConfig.setJdbcUrl(crmJdbcUrl);
        crmConfig.setUsername(crmDatabaseUserName);
        crmConfig.setPassword(crmDatabasePassword);
        if (StringUtils.isNotBlank(crmPoolName))
            crmConfig.setPoolName(crmPoolName);

        try {
            crmDataSource = new HikariDataSource(crmConfig);
            log.info("########## CRM DataSource Initialized Successfully ##############");
        } catch (Exception e) {
            log.error("Exception in create crmDataSource,JdbcUrl: {},userName: {},Exception is: {}",
                    crmConfig.getJdbcUrl(), crmConfig.getUsername(), e.getMessage());
        }

    }

    public static void initexternalDataSource() {


        String externalDriverClassName = ConfigUtils.getConfig("spring.datasource.driver-class-name");

        String externalDatabaseServer = ConfigUtils.getConfig("CR_DATABASE_SERVER");
        String externalDatabaseUserName = ConfigUtils.getConfig("CR_DATABASE_USER");
        String externalDatabasePassword = Encryptor.decrypt(ConfigUtils.getConfig("CR_DATABASE_USER_PASSWORD"));
        String externalDatabaseName = ConfigUtils.getConfig("external_DATABASE_NAME");

        int externalPoolSize = ConfigUtils.getConfigNumeric("externalDatabase.maximum-pool-size");
        long externalConnectionTimeout = ConfigUtils.getConfigNumeric("externalDatabase.connection-timeout");
        long externalMaxLifeTime = ConfigUtils.getConfigNumeric("externalDatabase.max-lifetime");
        long externalIdleTimeout = ConfigUtils.getConfigNumeric("externalDatabase.idle-timeout");
        long externalLeakDetectionThreshold = ConfigUtils.getConfigNumeric("externalDatabase.leak-detection-threshold");

        String externalPoolName = ConfigUtils.getConfig("externalDatabase.pool-name");

        if (externalPoolSize > 0)
            externalDatabaseConfig.setMaximumPoolSize(externalPoolSize);

        if (externalConnectionTimeout > 0)
            externalDatabaseConfig.setConnectionTimeout(externalConnectionTimeout);

        if (externalMaxLifeTime > 0)
            externalDatabaseConfig.setMaxLifetime(externalMaxLifeTime);

        if (externalIdleTimeout > 0)
            externalDatabaseConfig.setIdleTimeout(externalIdleTimeout);

        if (externalLeakDetectionThreshold > 0)
            externalDatabaseConfig.setLeakDetectionThreshold(externalLeakDetectionThreshold);

        externalDatabaseConfig.setDriverClassName(externalDriverClassName);

        String externalJdbcUrl = "jdbc:sqlserver://" + externalDatabaseServer + ";databaseName=" + externalDatabaseName
                + ";encrypt=false";
        externalDatabaseConfig.setJdbcUrl(externalJdbcUrl);
        externalDatabaseConfig.setUsername(externalDatabaseUserName);
        externalDatabaseConfig.setPassword(externalDatabasePassword);
        if (StringUtils.isNotBlank(externalPoolName))
            externalDatabaseConfig.setPoolName(externalPoolName);

        try {
            externalDataSource = new HikariDataSource(externalDatabaseConfig);
        } catch (Exception e) {
            log.error("Exception in create externalDataSource,JdbcUrl: {},userName: {},Exception is: {}",
                    externalDatabaseConfig.getJdbcUrl(), externalDatabaseConfig.getUsername(), e.getMessage());
        }
    }

    public static void closeDataSources() {
        log.info("crmDataSource SHUTDOWN");
        crmDataSource.close();
        log.info("externalDataSource SHUTDOWN");
        externalDataSource.close();
    }

    public static Connection getCRMConnection() throws SQLException {
        return crmDataSource.getConnection();
    }

    public static Connection getexternalConnection() throws SQLException {
        return externalDataSource.getConnection();
    }

    private HikariCPDataSource() {
    }
}

and call the init function on application context initialized.

ISSUE: when the database is down and I get some connection timeout exceptions as follows :

java.sql.SQLTransientConnectionException: crmPool - Connection is not available, request timed out after 36661ms.
    at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:100) ~[HikariCP-4.0.3.jar:?]
    at com.myapp.dao.HikariCPDataSource.getCRMConnection(HikariCPDataSource.java:131) ~[classes/:1]
    at com.myapp.dao.MyRepository.getConnection(MyRepository.java:22) ~[classes/:1]
    at com.myapp.beans.MyBean.findClientRelatedCRs(MyBean.java:207) ~[classes/:1]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_281]
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_281]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[?:1.8.0_281]
    at java.lang.reflect.Method.invoke(Unknown Source) ~[?:1.8.0_281]
    at org.apache.el.parser.AstValue.invoke(AstValue.java:252) ~[jasper-el.jar:9.0.71]
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:266) ~[jasper-el.jar:9.0.71]
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:107) ~[javax.faces-2.3.9.jar:2.3.9]
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87) ~[javax.faces-2.3.9.jar:2.3.9]
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102) ~[javax.faces-2.3.9.jar:2.3.9]
    at org.primefaces.application.DialogActionListener.processAction(DialogActionListener.java:54) ~[primefaces-12.0.0.jar:12.0.0]
    at javax.faces.component.UIViewAction.broadcast(UIViewAction.java:587) ~[javax.faces-2.3.9.jar:2.3.9]
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:870) ~[javax.faces-2.3.9.jar:2.3.9]
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1418) ~[javax.faces-2.3.9.jar:2.3.9]
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82) ~[javax.faces-2.3.9.jar:2.3.9]
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100) ~[javax.faces-2.3.9.jar:2.3.9]
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:201) ~[javax.faces-2.3.9.jar:2.3.9]
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:670) ~[javax.faces-2.3.9.jar:2.3.9]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-websocket.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.ocpsoft.rewrite.servlet.RewriteFilter.doFilter(RewriteFilter.java:226) ~[rewrite-servlet-3.4.4.Final.jar:3.4.4.Final]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:111) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:111) ~[primefaces-12.0.0.jar:12.0.0]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:111) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:111) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:111) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:711) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:459) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:385) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:313) ~[catalina.jar:9.0.71]
    at org.springframework.security.web.header.HeaderWriterFilter$HeaderWriterRequestDispatcher.forward(HeaderWriterFilter.java:170) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.ocpsoft.rewrite.servlet.impl.HttpRewriteResultHandler.handleResult(HttpRewriteResultHandler.java:42) ~[rewrite-servlet-3.4.4.Final.jar:3.4.4.Final]
    at org.ocpsoft.rewrite.servlet.RewriteFilter.rewrite(RewriteFilter.java:297) ~[rewrite-servlet-3.4.4.Final.jar:3.4.4.Final]
    at org.ocpsoft.rewrite.servlet.RewriteFilter.doFilter(RewriteFilter.java:198) ~[rewrite-servlet-3.4.4.Final.jar:3.4.4.Final]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.primefaces.webapp.filter.FileUploadFilter.doFilter(FileUploadFilter.java:111) ~[primefaces-12.0.0.jar:12.0.0]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at com.myapp.AjaxTimeoutFilter.doFilter(AjaxTimeoutFilter.java:71) ~[classes/:1]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:337) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:122) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:116) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:109) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:166) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:223) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:217) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at com.myapp.CaptchaAuthenticationFilter.doFilter(CaptchaAuthenticationFilter.java:72) ~[classes/:1]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:112) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:82) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.session.DisableEncodeUrlFilter.doFilterInternal(DisableEncodeUrlFilter.java:42) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186) ~[spring-security-web-5.7.3.jar:5.7.3]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) ~[spring-boot-actuator-2.7.4.jar:2.7.4]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:126) ~[spring-boot-2.7.4.jar:2.7.4]
    at org.springframework.boot.web.servlet.support.ErrorPageFilter.access$000(ErrorPageFilter.java:64) ~[spring-boot-2.7.4.jar:2.7.4]
    at org.springframework.boot.web.servlet.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:101) ~[spring-boot-2.7.4.jar:2.7.4]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:119) ~[spring-boot-2.7.4.jar:2.7.4]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.23.jar:5.3.23]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.23.jar:5.3.23]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[catalina.jar:9.0.71]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[catalina.jar:9.0.71]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[catalina.jar:9.0.71]
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:687) ~[catalina.jar:9.0.71]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[catalina.jar:9.0.71]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:360) ~[catalina.jar:9.0.71]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:399) ~[tomcat-coyote.jar:9.0.71]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-coyote.jar:9.0.71]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:891) ~[tomcat-coyote.jar:9.0.71]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1784) ~[tomcat-coyote.jar:9.0.71]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-coyote.jar:9.0.71]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-util.jar:9.0.71]
    at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-util.jar:9.0.71]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-util.jar:9.0.71]
    at java.lang.Thread.run(Unknown Source) ~[?:1.8.0_281]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 10.10.50.123, port 55225 has failed. Error: "The timeout expired before connecting to the routing destination.". ClientConnectionId:5d1c4012-43a1-409b-accd-dffd822cdfc7
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3795) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3006) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064) ~[mssql-jdbc-11.2.1.jre8.jar:?]
    at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:726) ~[HikariCP-4.0.3.jar:?]
    at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712) ~[HikariCP-4.0.3.jar:?]
    at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:1.8.0_281]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[?:1.8.0_281]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[?:1.8.0_281]
    ... 1 more

After that I get java.lang.OutOfMemoryError,exception message is: Java heap space

While enabling debug, I see the following logs :

crmPool - Closing connection ConnectionID:335466 ClientConnectionId: 16cef5bd-2ea3-44e5-a44f-ba5c66c0eeb1: (connection has passed maxLifetime)
crmPool - Add connection elided, waiting 0, queue 483
crmPool - After adding stats (total=117, active=0, idle=117, waiting=0)

This is strange also because the total should be 600 and also why the connections go to queue ?

Is there's something I need to change in my Configuration in order to avoid this issue ?

Mahmoud Saleh
  • 33,303
  • 119
  • 337
  • 498
  • 1
    Could you add stack trace of OOME? – Sergey Tsypanov Mar 29 '23 at 08:05
  • 1
    Are you sure that your SQL server is capable to handle that much connections ? Because the timeout might happen because the server refuses (or have trouble) to handle as many connections, and the OOM might just be a side-effect of the fact that you have 100+ connections alive client-side. Hikari provides a [pool sizing documentation](https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing) to help you understand all of it. – amanin Mar 31 '23 at 15:14

1 Answers1

1

for first question

This is strange also because the total should be 600 and also why the connections go to queue ?

that is a "connection" open queue as described here. And for second question check below.

Configuration is missing socket timeout configuration as described here

In order to avoid this condition, it is imperative that the application configures the driver-level TCP socket timeout. Each driver differs in how this timeout is set, but nearly all drivers support it.

HikariCP recommends that the driver-level socket timeout be set to (at least) 2-3x the longest running SQL transaction, or 30 seconds, whichever is longer. However, your own recovery time targets should determine the appropriate timeout for your application.

For MS SQL doc is here

connection URL jdbc:sqlserver://localhost;userName=sa;password=PASSW0RD;database=master;socketTimeout=10000;

If socketTimeout is not defined then that tcp connection waits forever and probably that creates the OutOfMemoryError.

In order to fix OutOfMemoryError, you can lower crm.maximum-pool-size=600 or increase/fine tune xmx and xms values of the jvm. last but not least why not upgrade to java 17. Java 8 is pretty old and every jvm brings more performance improvements.

ozkanpakdil
  • 3,199
  • 31
  • 48