# Handling java.sql.SQLRecoverableException: Closed Connection errors

#### お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

## 問題

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)
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 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.j

These can occur after a period of inactivity while transacting with Confluence's database - for example, while Confluence's XML backup executes.

## 原因

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 (DPCP) 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"
maxActive="20"
maxIdle="10"
maxWait="-1"
validationQuery="select 1 from dual"
/>


この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する