java.sql.SQLException: Already closed

Question

We have a webapp running in production on tomcat with a MySQL back-end. All was fine for sometime, then suddenly we started getting this exception java.sql.SQLException: Already closed.

The entire stack trace is:

DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Fetching JDBC Connection from DataSource
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Returning JDBC Connection to DataSource
DEBUG [org.springframework.jdbc.datasource.DataSourceUtils] Could not close JDBC Connection    
java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
    at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:333)
    at org.springframework.jdbc.datasource.DataSourceUtils.releaseConnection(DataSourceUtils.java:294)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:405)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)
    at com.nokia.analytics.aws.aggregate.service.importer.DBInsert.truncateTable(DBInsert.java:135)
    at com.blah.analytics.aggregate.service.importer.AggregateCollector.pullAndInsert(AggregateCollector.java:85)
    at com.blah.analytics.aggregate.service.importer.AggregateCollector.call(AggregateCollector.java:96)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1146)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:679)

We are using org.apache.commons.dbcp.BasicDataSource as our datasource. I searched quite a bit but to no avail. It doesn't occur always and hence is very hard to reproduce. It seems a problem with db connection pooling. Somewhere it was suggested to set this param as negative. Currently we are not changing of those parameters (all have default vals).

What approach should we follow to avoid it?

EDIT:

The relevant code is in (DBInsert.java)

133: String sql = "DELETE FROM "+tableName;

134: logger.debug(sql);

135: this.jdbcTemplate.execute(sql);

(133-135 are line nos. which are specified in the exception)

My datasource config:

<bean id="bisToolDataSource" class="org.apache.commons.dbcp.BasicDataSource"
        destroy-method="close">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url"
            value="${url}/blah_db?verifyServerCertificate=false&amp;useSSL=true&amp;requireSSL=true" />
        <property name="username" value="${uname}" />
        <property name="password" value="${passwd}" />
    </bean>

I don't know what causes it, but it is a bug, [`Connection.close()`](http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()) should not throw an exception if already closed: _"Calling the method `close` on a `Connection` object that is already closed is a no-op"_ — Mark Rotteveel, May 02 '13 at 06:50

score 18 · Answer 1 · answered May 02 '13 at 09:41

18

The cause of this problem is connection isn't used in a long time, add testOnBorrow and validationQuery property to your datasource configuration then your application will work fine.
Good luck:)

answered May 02 '13 at 09:41

Hunter Zhao

4,589
2
25
39

+1 You are absolutely right in spotting the problem. I had already figured out that problem was related to the connection pooling and turned off connection pooling. – Harshal Waghmare May 03 '13 at 06:01
Well the only reason I didn't accept is because I didn't try out the approach suggested by you... – Harshal Waghmare May 07 '13 at 13:01
@HarshalWaghmare , would like to ask what do you means by turned off connection pooling? My application is running on IBM WAS, and using db2 as database. Mostly what I see the connection pool setting can do is about the maximum connection and minimum connect size. But not understand about the turned off setting, can advice? – Panadol Chong Jan 15 '18 at 09:37

score 7 · Answer 2 · edited May 23 '17 at 12:09

As user NobodyElse pointed out, the problem was related to connection pooling. I was using org.apache.commons.dbcp.BasicDataSource as datasource. The nature of the application is such that there is spurt of connections at some fixed time in the day and no connections at all for the entire day. So due to this connections in the pool were getting stale and when next day application tried to connect to DB, we were getting this exception.

There are basically two solutions to this: The one pointed out by NobodyElse, that is to use testOnBorrow; details can be found here

The other solution (which I employed for our app) is to turn off pooling completely. Note do this only when the application is not DB intensive (which was true in our case). So I switched to org.springframework.jdbc.datasource.DriverManagerDataSource. The config for which it seems to be working fine is:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url"
            value="${url}/blah_db?verifyServerCertificate=false&amp;useSSL=true&amp;requireSSL=true" />
        <property name="username" value="${uname}" />
        <property name="password" value="${passwd}" />
</bean>

FYI...."DriverManagerDataSource" is not really connection pool. http://docs.spring.io/spring/docs/3.2.x/javadoc-api/org/springframework/jdbc/datasource/DriverManagerDataSource.html — kosa, Jan 02 '14 at 21:19

java.sql.SQLException: Already closed

2 Answers2

Linked