java.sql.Exception ClosedConnection

Question

I am getting the following error:

java.sql.SQLException: Closed Connection at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) at oracle.jdbc.driver.PhysicalConnection.getMetaData(PhysicalConnection.java:1508) at com.ibatis.sqlmap.engine.execution.SqlExecutor.moveToNextResultsSafely(SqlExecutor.java:348) at com.ibatis.sqlmap.engine.execution.SqlExecutor.handleMultipleResults(SqlExecutor.java:320) at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQueryProcedure(SqlExecutor.java:277) at com.ibatis.sqlmap.engine.mapping.statement.ProcedureStatement.sqlExecuteQuery(ProcedureStatement.java:34) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173) at com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList(GeneralStatement.java:123) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:614) at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:588) at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118) at org.springframework.orm.ibatis.SqlMapClientTemplate$3.doInSqlMapClient(SqlMapClientTemplate.java:268) at org.springframework.orm.ibatis.SqlMapClientTemplate.execute(SqlMapClientTemplate.java:193) at org.springframework.orm.ibatis.SqlMapClientTemplate.executeWithListResult(SqlMapClientTemplate.java:219) at org.springframework.orm.ibatis.SqlMapClientTemplate.queryForList(SqlMapClientTemplate.java:266) at gov.hud.pih.eiv.web.authentication.AuthenticationUserDAO.isPihUserDAO(AuthenticationUserDAO.java:24) at gov.hud.pih.eiv.web.authorization.AuthorizationProxy.isAuthorized(AuthorizationProxy.java:125) at gov.hud.pih.eiv.web.authorization.AuthorizationFilter.doFilter(AuthorizationFilter.java:224) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246) at

I am really stumped and can't figure out what could be causing this error. I am not able to reproduce the error on my machine but on production it is coming a lot of times. I am using iBatis in the whole application so there are no chances of my code not closing connections.

We do have stored procedures that run for a long time before they return results (around 15 seconds).

does anyone have any ideas on what could be causing this? I dont think raising the # of connections on the application server will fix this issue buecause if connections were running out then we'd see "Error on allocating connections"

Sample code snippet:

this.setSqlMapClientTemplate(getSqlTempl());
getSqlMapClientTemplate().queryForList("authentication.isUserDAO", parmMap);
this.setSqlMapClientTemplate(getSqlTemplDW());
List results = (List) parmMap.get("Result0");

I am using validate in my connection pool.

Answer 1

Based on the stack trace, the likely cause is that you are continuing to use a ResultSet after close() was called on the Connection that generated the ResultSet.

Answer 2

What is your DataSource framework? Apache Commons DBCP? do you use poolPrepareStatement property in data source configuration?

Check the following:

1. Make sure testOnBorrow and testOnReturn are true and place a simple validationQuery like select 0 from dual. Do you use au
2. do you use autoCommit? Are you using START TRANSACTION, COMMIT in your stored procedures? After several days of debugging we found out that you can't mix transaction management both in Java and in SQL - you have to decide on one place to do it. Where are you doing yours?

Edit your question with answers to this, an we'll continue from there.

Answer 3

When a db server reboots, or there are some problems with a network, all the connections in the connection pool are broken and this usuall requires a reboot of application server

And if broken connection detected, you shold create a new one to replace it in connection pool. It's common problem called deadly connections.