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

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>ibm.db2</groupId>
    <artifactId>db2jcc4.jar</artifactId>
    <version>4.19.26</version>
</dependency>

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

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

List<Something> 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.

I believe I do? I think `spring-boot-starter-jdbc` uses tomcat-jdbc pooling as default — bscott, Oct 14 '16 at 20:04
thanks for tracking this 6 months later, accept the response and share your thoughts! — exoddus, Sep 07 '17 at 11:39

score 4 · Accepted Answer · edited May 23 '17 at 11:53

4

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.

edited May 23 '17 at 11:53

Community

1
1

answered Oct 14 '16 at 18:38

exoddus

2,230
17
27

Also have a look at the Spring Boot section about connection pools: http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html#boot-features-connect-to-production-database – Ralph Oct 14 '16 at 18:40
Thanks, I will implement these for now and see if this fixes the issue. Because it usually takes 1-3 days for the "Connection is Closed." error to occur, I will wait until say Friday next week for the error to occur – bscott Oct 14 '16 at 20:09
I updated the values of the properties you provided and added a few more: `ds1.datasource.validation-query="SELECT 1 FROM SYSIBM.SYSDUMMY1"` and `ds1.datasource.initial-size=10`. Again, this is kind of hard to validate/test since the 'Connection is closed.' issue was appearing every few days or so. I will keep the application up and running for the rest of this week. If the 'Connection is closed.' issue doesn't appear after the end of the week, I will assume this solution worked and mark this as the correct answer. – bscott Oct 17 '16 at 21:06
you can also try to change the debug level of the package is throwing that exception and try to catch more info. Hope it helps :) – exoddus Oct 18 '16 at 07:44
@exoddus I will consider doing that as well. Still going to mark this as the correct answer though. It has been exactly 1 week since I implemented these datasource properties and I haven't had a "Connection is closed" exception thrown once. All queries are being executed normally. However, I will continue to monitor this and add/remove/update datasource properties as needed should the issue ever pop up again. – bscott Oct 24 '16 at 17:14
I'm actually unmarking this as the answer for now. The issue has creeped back again. This time I'm able to log it. Check my question for updates. – bscott Oct 26 '16 at 22:08
Did you solve it? We are running into this same problem about once per week – cocorossello Nov 27 '17 at 15:08

Spring jdbcTemplate - Connection is Closed

1 Answers1