Spring Throws "ERROR SqlExceptionHelper: Already closed" after timeout

Question

I have a spring application hosted on to the server (Tomcat 8.5). It goes idle if no one uses it. I already knew that timeout will occur if the DB is in idle state for 8 hours (Default timeout of MySQL). As mentioned in Spring Autoreconnect and Connection lost overnight post i have tried the solution available here.I have tried configuring application.properties but that doesn't bring any solution to the problem.

(PS:I'm not changing anything other than application.properties in my Spring Application).

score 0 · Answer 1 · answered Nov 19 '19 at 06:56

Well if this

spring.datasource.testWhileIdle = true
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.validationQuery = SELECT 1

or this

spring.datasource.testOnBorrow=true
spring.datasource.validationQuery=SELECT 1

didnt work maybe try this

Post SpringBoot 1.4 names have changed

They have defined new specific namespaces for the four connections pools spring supports: tomcat, hikari, dbcp, dbcp2.

spring.datasource.tomcat.testOnBorrow=true
spring.datasource.tomcat.validationQuery=SELECT 1

Bhuvaneshwaran Ilanthirayan · Accepted Answer · 2019-12-06T06:11:59.720

If problem doesn't solve even after including properties as in application.properties, Then problem will be solved when including testOnBorrow,validationQuery in application-context.xml located in src/main/resources

<bean name="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${database.driver.classname}"/>
    <property name="url" value="${database.url}"/>
    <property name="username" value="${database.username}"/>
    <property name="password" value="${database.password}"/>
    <property name="initialSize" value="2"/>
    <property name="maxActive" value="50"/>
    <property name="maxIdle" value="5"/>
    <property name="maxWait" value="-1"/>
    <property name="removeAbandoned" value="true"/>
    <property name="removeAbandonedTimeout" value="600"/>
    <property name="logAbandoned" value="true"/>
    <property name="testOnBorrow" value="true" />
    <property name="validationQuery" value="SELECT 1" />        
</bean>

The solution is to validate connection thread when it is borrowed from thread pool by enabling testOnBorrow and providing validationQuery.

Spring Throws "ERROR SqlExceptionHelper: Already closed" after timeout

2 Answers2