Handling java.sql.SQLRecoverableException: Closed Connection errors
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Sometimes, errors like the following samples appear in the atlassian-confluence.log:
2020-06-04 12:58:39,068 ERROR [http-nio-26136-exec-9] [engine.jdbc.spi.SqlExceptionHelper] logExceptions Cannot close connection (invalidating pooled object failed)
-- url: /c6136/admin/dobackup.action | traceId: 7cfdc86875a87aa1 | referer: http://localhost:26136/c6136/admin/backup.action
2020-06-04 12:58:39,106 ERROR [http-nio-26136-exec-9] [[Standalone].[localhost].[/c6136].[action]] log Servlet.service() for servlet [action] in context with path [/c6136] threw exception
org.springframework.transaction.TransactionSystemException: Could not commit Hibernate transaction; nested exception is net.sf.hibernate.TransactionException: Unable to commit against JDBC Connection
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:514)
at com.atlassian.confluence.impl.hibernate.ConfluenceHibernateTransactionManager.doCommit(ConfluenceHibernateTransactionManager.java:69)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:765)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:734)
(...)
Caused by: net.sf.hibernate.TransactionException: Unable to commit against JDBC Connection
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:85)
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:221)
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:68)
at com.atlassian.hibernate.util.TransactionHelper$RestartOnCommitTransaction.commit(TransactionHelper.java:45)
at com.atlassian.hibernate.adapter.adapters.TransactionV2Adapter.commit(TransactionV2Adapter.java:30)
at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510)
... 204 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
at oracle.jdbc.driver.PhysicalConnection.getAutoCommit(PhysicalConnection.java:1828)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:1905)
at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:1942)
at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:358)
at org.apache.tomcat.dbcp.dbcp2.DelegatingConnection.commit(DelegatingConnection.java:358)
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:79)The sample above occurred while using an Oracle database. Here's another variation for the same kind of database:
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.rollback(PhysicalConnection.java:1170)
at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:368)
at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:323)
at net.sf.hibernate.transaction.JDBCTransaction.rollback(JDBCTransaction.java:86)
at org.springframework.orm.hibernate.HibernateTransactionManager.doRollback(HibernateTransactionManager.java:529)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processRollback(AbstractPlatformTransactionManager.java:753)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:730)
at org.springframework.transaction.interceptor.TransactionAspectSupport.completeTransactionAfterThrowing(TransactionAspectSupport.jThese can occur after a period of inactivity while transacting with Confluence's database - for example, while Confluence's XML backup executes.
Cause
This error message indicates that the connection used by the backup task (or any other long-running operation that relies on a single database connection) was closed while the transaction was executing. This can occur for different reasons. For example, there might be an external device like a firewall, network device, or remote database listener that can force network connections to close after a certain period of inactivity (idle) or how long the connection was open (TTL).
When this type of situation occurs, then the database connection pool (DBCP) or C3PO (if using direct JDBC) is not able to detect this connection termination. It is only when Confluence attempts to use a connection from the pool that a 'Closed Connection' exception is raised.
Possible Resolutions
Check the Oracle database settings
From the database side (in this case, Oracle), the DBA should look for any configurations that could close a connection. For reference, please see:
Check Linux operating system settings
In the case of the XML backup job, it'll use a connection from the database connection pool and hold onto it until the end of the job. Because the connection is no longer in the pool and is idle until the end of the transaction, it doesn't get evaluated with the c3p0.idle_test_period property and can be wiped out by a firewall (or any other network component) idle timeout.
In this case, please make sure that the tcp_keepalive_time setting on the Operating System side (Confluence server/nodes) has a value that will allow the database transaction to execute without interruption. For reference, please see:
Check that a validation query is configured
Refer to the document Surviving Connection Closures to add in validation queries for your database. This will allow the datasource connection to run a validation query against the database before usage to verify the connection is usable.
Add an additional connection property: 'oracle.net.READ_TIMEOUT=5000' for jdbc versions < 10.1.0.5 or 'oracle.jdbc.ReadTimeout=5000' for jdbc versions >=10.1.0.5 to the resource definition.
The following is for Oracle databases only:
<Resource name="jdbc/ConfluenceDS"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@my.host.edu:PORT:SID"
username="***MASKED***"
password="***MASKED***"
connectionProperties="SetBigStringTryClob=true; oracle.net.READ_TIMEOUT=5000;"
maxActive="20"
maxIdle="10"
maxWait="-1"
validationQuery="select 1 from dual"
/>
Check for maximum connection age under respective Pool settings
- For JNDI datasource connection to Database i.e https://confluence.atlassian.com/doc/configuring-a-datasource-connection-937166084.html below properties will be of interest if configured under Tomcat server.xml-
- removeAbandoned and removeAbandonedTimeout. By default under Tomcat, removeAbandoned property has been set to false but then we have seen instances where this has been set to true and this can cause connection to be abandoned based on the value set under removeAbandonedTimeout. The default value of removeAbandonedTimeout is 60 secs which would mean that post 1 min of activity, the connection will be abandoned which can cause Closed Connection exception to be seen across connection if some task takes more than 1 min of time for processing. You can find more details around these properties under https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html
- For direct JDBC connection to Database(default setup) below property will be of interest if configured under confluence.cfg.xml file
- maxConnectionAge - Based on the value configured under this, the pool will pull out the connection from use if any connection that was acquired from the database crosses this value since initiation. This again can cause Closed Connection exception as seen above. You can find more details around this under - https://www.mchange.com/projects/c3p0/#managing_pool_size