Spring jdbcTemplate - Connection is Closed

Question

I've had a long running issue with my Jersey REST application that utilizes Spring jdbcTemplate to make basic SELECT, INSERT, UPDATE, and DELETE queries to our database (we use DB2).

This issue happens every few days so I don't have a System.out of the contents of the error (next time it happens I will include a screenshot of the error). Every few days or so, some of the queries in my REST services start failing due to a "Connection is Closed." error. Whenever I get this error, I simply restart the tomcat application server and the issue is resolved for a few days until it starts happening again.

Restarting the server every few days will not be an acceptable solution to our end users once they start using this. So if anyone has any idea why this happens and how I can go about resolving the issue permanently, please let me know.

Here's my Spring datasource config:

package com.my.package;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {

    @Bean (name = "dataSource1")
    @Primary
    @ConfigurationProperties(prefix = "ds1.datasource")
    public DataSource dataSource1() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "ds1")
    public JdbcTemplate jdbcTemplate1(@Qualifier("dataSource1") DataSource dataSource1) {
        return new JdbcTemplate(dataSource1);
    }

    @Bean (name = "dataSource2")
    @ConfigurationProperties(prefix="ds2.datasource")
    public DataSource dataSource2() { return DataSourceBuilder.create().build(); }

    @Bean(name = "ds2")
    public JdbcTemplate jdbcTemplate2(@Qualifier("dataSource2") DataSource dataSource2) {
        return new JdbcTemplate(dataSource2);
    }
}

Here's my application.properties:

ds1.datasource.url=url1
ds1.datasource.username=user1
ds1.datasource.password=pass1
ds1.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver

ds2.datasource.url=url2
ds2.datasource.username=user2
ds2.datasource.password=pass2
ds2.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver

My pom.xml where I include the spring jdbc and driver dependencies


    org.springframework.boot
    spring-boot-starter-jdbc


    ibm.db2
    db2jcc4.jar
    4.19.26

And lastly, an example of me executing a simple SELECT query using jdbcTemplate

@Autowired
@Qualifier("ds1")
private JdbcTemplate jdbcTemplate;

List sampleQuery(){
    String sqlQuery = "SELECT * FROM TABLE";
    try {
        return this.jdbcTemplate.query(
                sqlQuery,
                (rs, rowNum) -> {

                    Something something = new Something();
                    something.setVal1(rs.getString("FIELD1").trim());
                    something.setVal2(rs.getString("FIELD2").trim());

                    return something;
                });
    }catch (Exception ex){
        ex.printStackTrace();
        System.out.println("error...");
        return new ArrayList<>();
    }
}

EDIT: Error is still happening. This time I'm able to capture the log. It basically just says "Failed to validate a newly established connection." I don't know the why though.

org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: Failed to validate a newly established connection.
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:615)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:680)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:722)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:772)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
    at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.boot.actuate.autoconfigure.EndpointWebMvcAutoConfiguration$ApplicationContextHeaderFilter.doFilterInternal(EndpointWebMvcAutoConfiguration.java:261)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.boot.actuate.trace.WebRequestTraceFilter.doFilterInternal(WebRequestTraceFilter.java:115)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:87)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:121)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.springframework.boot.actuate.autoconfigure.MetricsFilter.doFilterInternal(MetricsFilter.java:103)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1502)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1458)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Failed to validate a newly established connection.
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:811)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.borrowConnection(ConnectionPool.java:626)
    at org.apache.tomcat.jdbc.pool.ConnectionPool.getConnection(ConnectionPool.java:185)
    at org.apache.tomcat.jdbc.pool.DataSourceProxy.getConnection(DataSourceProxy.java:127)
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
    ... 81 more

EDIT#2: Finally marking @exoddus's answer as the correct one. After reading his answer and looking through the Spring JDBC documentation he provided (which led me to the underlying tomcat JDBC connection pool documentation), I started playing around with a lot of different properties until it just worked.

Here is the current configuration I use in my production environment:

ds1.datasource.url=jdbc:db2://database.domain.com:12345/DBMS
ds1.datasource.username=admin
ds1.datasource.password=admin
ds1.datasource.driver-class-name=com.ibm.db2.jcc.DB2Driver
ds1.datasource.max-active=200
ds1.datasource.max-idle=200
ds1.datasource.max-wait=20000
ds1.datasource.min-idle=50
ds1.datasource.test-while-idle=true
ds1.datasource.test-on-borrow=true
ds1.datasource.validation-query=SELECT 1 FROM SYSIBM.SYSDUMMY1
ds1.datasource.time-between-eviction-runs-millis=30000
ds1.datasource.remove-abandoned=true
ds1.datasource.remove-abandoned-timeout=30
ds1.datasource.abandon-when-percentage-full=50
ds1.datasource.initial-size=50
ds1.datasource.jdbcInterceptors=ResetAbandonedTimer

Repeat for ds2. This configuration has been working just fine since I implemented it roughly 6 months ago.

exoddus · Accepted Answer

Based in your context I would try to configure some parameters of the DataSource beans. Maybe after some days you get out of connections because some of them are never released or finished (i just guess).

Try adding that properties to your application.properties:

ds1.datasource.max-active=50
ds1.datasource.max-idle=8
ds1.datasource.max-wait=10000
ds1.datasource.min-idle=4
ds1.datasource.test-on-borrow=true

same for the ds2

The correct values depends on your environtment/hardware. Take a look here for a nice and concise explanation about datasources and pools.

Here you can find another interesting question about DataSource on spring that may help you.

Spring jdbcTemplate - Connection is Closed

Answers (1)

Related Questions