Login/Register
HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2a84e649 (This connection has been closed.)
Asked Answered
postgresqlspring-bootspring-jdbchikaricp
I

2

13

I'm using Postgresql and spring boot 2.0.4. The below error is thrown when trying to execute the queries one after the other. I've executed the following query, and the count keeps on increasing. 

SELECT COUNT(*) FROM pg_stat_activity WHERE state ILIKE '%idle%';

I've used these properties and dependencies for connection pool. Yet, it gives the same error

spring.datasource.dbcp2.initial-size=10
spring.datasource.dbcp2.max-total=25
spring.datasource.dbcp2.pool-prepared-statements=true
spring.datasource.hikari.max-lifetime=600000

<dependency>
        <groupId>org.apache.commons</groupId>
        <artifactId>commons-dbcp2</artifactId>
        <version>2.1.1</version>
</dependency>

Error:

2018-09-27 16:43:25.481  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@2a84e649 (This connection has been closed.)
    2018-09-27 16:43:40.490  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@9faf6ae (This connection has been closed.)
    2018-09-27 16:44:00.934  WARN 9085 --- [nio-8081-exec-4] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Failed to validate connection org.postgresql.jdbc.PgConnection@24723851 (This connection has been closed.)
    2018-09-27 16:44:00.935  WARN 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08003
    2018-09-27 16:44:00.936 ERROR 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : HikariPool-1 - Connection is not available, request timed out after 49443ms.
    2018-09-27 16:44:00.936  WARN 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 08003
    2018-09-27 16:44:00.936 ERROR 9085 --- [nio-8081-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper   : This connection has been closed.
    2018-09-27 16:44:00.938 ERROR 9085 --- [nio-8081-exec-4] com.netelixir.dao.EmailStatsDao          : Exception occured due to javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection

Edit - 1

WARN 11920 --- [l-1 housekeeper] com.zaxxer.hikari.pool.ProxyLeakTask     : Connection leak detection triggered for org.postgresql.jdbc.PgConnection@603cfde6 on thread http-nio-8081-exec-6, stack trace follows

java.lang.Exception: Apparent connection leak detected
    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128) ~[HikariCP-2.7.9.jar:na]
    at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.NonContextualJdbcConnectionAccess.obtainConnection(NonContextualJdbcConnectionAccess.java:35) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.acquireConnectionIfNeeded(LogicalConnectionManagedImpl.java:106) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.resource.jdbc.internal.LogicalConnectionManagedImpl.getPhysicalConnection(LogicalConnectionManagedImpl.java:136) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:47) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:145) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:171) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:147) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1985) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1915) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1893) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:938) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2692) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2675) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2507) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:502) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:392) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1490) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1445) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1414) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at org.hibernate.query.Query.getResultList(Query.java:146) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]
    at com.netelixir.dao.EmailStatsDao.getEmailStatsTableData(EmailStatsDao.java:146) ~[classes/:na]
    at com.netelixir.dao.EmailStatsDao$$FastClassBySpringCGLIB$$c924bff4.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:746) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688) ~[spring-aop-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at com.netelixir.dao.EmailStatsDao$$EnhancerBySpringCGLIB$$5e0a7d71.getEmailStatsTableData(<generated>) ~[classes/:na]
    at com.netelixir.service.AdminDashBoardService.getLastSevenDaysData(AdminDashBoardService.java:43) ~[classes/:na]
    at com.netelixir.controller.AdminDashBoardController.getChartData(AdminDashBoardController.java:44) ~[classes/:na]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_181]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_181]
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:866) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) ~[spring-webmvc-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.CorsFilter.doFilterInternal(CorsFilter.java:96) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:147) ~[spring-session-core-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:81) ~[spring-session-core-2.0.5.RELEASE.jar:2.0.5.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.8.RELEASE.jar:5.0.8.RELEASE]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at com.netelixir.config.RequestFilter.doFilter(RequestFilter.java:33) ~[classes/:na]
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:493) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:800) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:800) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1471) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_181]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_181]
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-8.5.32.jar:8.5.32]
    at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181]

EmailStatsDao

import com.nidhi.model.EmailReportStats;
import com.nidhi.repository.EmailReportsRepository;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.PersistenceContext;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.query.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

/**
 *
 * @author nidhi
 */
@Repository
@Transactional
@Component
public class EmailStatsDao implements EmailReportsRepository {

    private static final Logger LOG = LoggerFactory.getLogger(EmailStatsDao.class);

    @Autowired
    EmailReportsRepository emailReportsRepository;

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    @PersistenceContext
    public EntityManager em;

    @Override
    public List<EmailReportStats> findByclientId(int clientId) {
        return emailReportsRepository.findByclientId(clientId);
    }

    @Override
    public <S extends EmailReportStats> S save(S s) {
        return emailReportsRepository.save(s);

    }

    @Override
    public <S extends EmailReportStats> Iterable<S> saveAll(Iterable<S> itrbl) {
        return emailReportsRepository.saveAll(itrbl);
    }

    @Override
    public Optional<EmailReportStats> findById(Integer id) {
        return emailReportsRepository.findById(id);

    }

    @Override
    public boolean existsById(Integer id) {
        return emailReportsRepository.existsById(id);

    }

    @Override
    public Iterable<EmailReportStats> findAll() {
        return emailReportsRepository.findAll();

    }

    @Override
    public Iterable<EmailReportStats> findAllById(Iterable<Integer> itrbl) {
        return emailReportsRepository.findAllById(itrbl);
    }

    @Override
    public long count() {
        return emailReportsRepository.count();
    }

    @Override
    public void deleteById(Integer id) {

        emailReportsRepository.deleteById(id);
    }

    @Override
    public void delete(EmailReportStats t) {
        emailReportsRepository.delete(t);

    }

    @Override
    public void deleteAll(Iterable<? extends EmailReportStats> itrbl) {
        emailReportsRepository.deleteAll();

    }

    @Override
    public void deleteAll() {
        emailReportsRepository.deleteAll();

    }

    public List<EmailReportStats> getEmailStatsData(String pSqlQuery) {
        List<EmailReportStats> emailReportStatses = new ArrayList<>();
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
            emailReportStatses = session.createNativeQuery(pSqlQuery).addEntity(EmailReportStats.class).list();
            if(!emailReportStatses.isEmpty()){
//                DateTimeFormatter formatter=DateTimeFormatter.ofPattern("dd MMM");
                for (EmailReportStats emailReport : emailReportStatses) {
                    emailReport.setDate(LocalDate.parse(emailReport.getDate()).format(DateTimeFormatter.ofPattern("dd MMM")));
                    emailReportStats.add(emailReport);
                }
            }
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }

    public List<EmailReportStats> getEmailStatsTableData(String pSqlQuery, int clientId) {
        List<EmailReportStats> emailReportStatses = new ArrayList<>();
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        List<Object[]> data=new ArrayList();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
           data=entityManagerFactory.createEntityManager().createQuery(pSqlQuery).getResultList();

//           data =  q.list();
            for(Object[] list: data){
                EmailReportStats reportStats=new EmailReportStats();
                 DecimalFormat decimalFormat = new DecimalFormat("#.##");
                reportStats.setRevenue(Float.valueOf(decimalFormat.format((Double)list[0])));
                reportStats.setOrders(Float.valueOf(decimalFormat.format((Double)list[1])));
                reportStats.setimpressions(((Long)list[2]).intValue());
                reportStats.setAccountId((Integer)list[3]);
                reportStats.setSearchEngineId((Integer)list[4]);
                reportStats.setDateType((String)list[5]);
            emailReportStats.add(reportStats);

            }
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }

    public List<EmailReportStats> getEmailSparkLinesData(String pSqlQuery) {
        List<EmailReportStats> emailReportStats = new ArrayList<>();
        Session session = entityManagerFactory.unwrap(SessionFactory.class).openSession();
        try {
            emailReportStats = session.createNativeQuery(pSqlQuery).addEntity(EmailReportStats.class).list();
        } catch (Exception exception) {
            LOG.error("Exception occured due to " + exception);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        return emailReportStats;
    }
}
Integrant answered 27/9, 2018 at 11:34 Comment(2)
Why are you setting dbcp2 settings if you are usingHikari? Set the min and max for Hikari make sure they differ. If you don't connections won't be checked if they are valid while in the pool . – Judejudea
@Deinum, I've removed the dbcp2 properties and added the following properties. The frequency of the error has decreased a bit, but the issue still exists. Any help would be appreciated. spring.datasource.hikari.minimumIdle=5 spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.maxLifetime=1800000 – Integrant
C
9

In Spring Boot 2.x, Hikari is default connection pool so if you have JPA then you don't need to add Hikari dependency in pom but if you want to use dbcp2 then you need to exclude Hikari and add dbcp2 dependency.

So in your case you mixed settings for both.

if just would like to stay with Hikari then remove dbcp2 dependencies and change your data source properties to use spring.datasource.hikari.

Add below properties to validate connection:

spring.datasource.testWhileIdle=true
spring.datasource.test-on-borrow=true

To further check if there is any leak, please add below properties and check how goes your pool connection and if there is any leak.

spring.datasource.hikari.leakDetectionThreshold=2000
logging.level.com.zaxxer.hikari=DEBUG
Cavalry answered 27/9, 2018 at 13:1 Comment(22)
Hi kj007, I've removed the dbcp2 properties and added the following properties. The frequency of the error has decreased a bit, but the issue still exists. Any help would be appreciated. spring.datasource.hikari.minimumIdle=5 spring.datasource.hikari.maximumPoolSize=20 spring.datasource.hikari.connectionTimeout=30000 spring.datasource.hikari.idleTimeout=600000 spring.datasource.hikari.maxLifetime=1800000 – Integrant
Please don't advice to set a validationQuery... That is only for drivers that don't support JDBC4 bit with java8 that isn't the case anymore. JDBC 4 has better means of validating a connection. – Judejudea
@Integrant one question how long your query is taking ?? is taking more than ideal connection timeout or connection timeout?? – Cavalry
@Cavalry I've made the changes as per your suggestion and it seems like there's a connection leak. Could you help me out in fixing it. – Integrant
also show me logs it will be showing you like : for After cleanup stats (total=10, active=0, idle=10, waiting=0) – Cavalry
@Cavalry I've added the leak detection log under my question with Edit - 1 name. Following is the log for cleanup stats 18:57:34.622 DEBUG 11920 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - Before cleanup stats (total=9, active=4, idle=5, waiting=0) 2018-09-27 18:57:34.622 DEBUG 11920 --- [l-1 housekeeper] com.zaxxer.hikari.pool.HikariPool : HikariPool-1 - After cleanup stats (total=9, active=4, idle=5, waiting=0) – Integrant
one more thing to check if you are directly making direct connection then by opening a connection then make sure close that connection..and also would like to how long your query is taking and how many request are coming ?? – Cavalry
check you are closing connection here EmailStatsDao.getEmailStatsTableData(EmailStatsDao.java:146) – Cavalry
Just make sure you are closing connection after execute query as seems in your getting email stats data is having some issue or what is average time in executing this query?? – Cavalry
@Cavalry I've added the method for querying the data under my question with the name EmailStatsDao. The time to execute the query from the code is varying, meaning, when i run the application the query executes within 4-5 seconds for the first time and later on it kind of take 10-15 seconds to excute. – Integrant
And how many request you are getting ?? Update your ideal timeout and connection timeout to 60000 both and increase little more max connection pool to let’s say 50, in mean time let me see your dal – Cavalry
One more questions after setting borrow property are you still see error for acquire connection ? – Cavalry
@Cavalry I've made the changes as suggested, the frequency of error has decreased significantly, yet it still arises. I've set the borrow property to true as well. One point that I've observed is, the cleanup stats keep on increasing. cleanup stats (total=50, active=50, idle=0, waiting=1) – Integrant
That’s the problem it’s not closing the connections. – Cavalry
It’s decrease because you increase pool size which giving more time to consume all but even after 50 next is waiting to connect – Cavalry
Could you help me out in fixing it, i mean how to close the connections. I've shared my DAO class as well. – Integrant
Show me your class on top what you are autowiring, you are closing session but need to loook at complete class – Cavalry
@Cavalry I've edited my EmailStatsDao. I've added my entire DAO class. – Integrant
Will look at shortly – Cavalry
Can you also try to close factory session in finally, I need to reproduce at my end to see actual issue so will do little later today – Cavalry
Let us continue this discussion in chat. – Cavalry
@Cavalry what are these Leaks exactly? are they on the hibernate/jpa layer or HIkari layer or on the DB level (postgres db)? I'm seeing similiar issues in my Spring Boot Web API which uses hibernate/JPA and my Db connections on my AWS RDS postgres DB go to 1500 and my API returns JDBCConnectionException and HTTP 500 server error – Roselynroseman
F
0

This is an answer for a slightly alternate case. We have this running in kubernetes and we are getting these same messages

Failed to validate connection org.postgresql.jdbc.PgConnection@2a84e649 (This connection has been closed.)

And after trying the above suggestions without success I looked a little deeper and found that we were only getting these message when the kubernetes was shutting the app down during a scale-down situation where spring-boot actuator was trying to get a database status at the same time. So I think it's fair to say that these can be ignored in this case.

Ferromagnetism answered 28/9, 2022 at 15:51 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.