Java sql sqlrecoverableexception closed connection

Class SQLRecoverableException

The subclass of SQLException thrown in situations where a previously failed operation might be able to succeed if the application performs some recovery steps and retries the entire transaction or in the case of a distributed transaction, the transaction branch. At a minimum, the recovery operation must include closing the current connection and getting a new connection.

Constructor Summary

Method Summary

Methods declared in class java.sql.SQLException

Methods declared in class java.lang.Throwable

Methods declared in class java.lang.Object

Methods declared in interface java.lang.Iterable

Constructor Details

SQLRecoverableException

Constructs a SQLRecoverableException object. The reason , SQLState are initialized to null and the vendor code is initialized to 0. The cause is not initialized, and may subsequently be initialized by a call to the Throwable.initCause(java.lang.Throwable) method.

SQLRecoverableException

Constructs a SQLRecoverableException object with a given reason . The SQLState is initialized to null and the vendor code is initialized to 0. The cause is not initialized, and may subsequently be initialized by a call to the Throwable.initCause(java.lang.Throwable) method.

SQLRecoverableException

Constructs a SQLRecoverableException object with a given reason and SQLState . The cause is not initialized, and may subsequently be initialized by a call to the Throwable.initCause(java.lang.Throwable) method. The vendor code is initialized to 0.

SQLRecoverableException

Constructs a SQLRecoverableException object with a given reason , SQLState and vendorCode . The cause is not initialized, and may subsequently be initialized by a call to the Throwable.initCause(java.lang.Throwable) method.

SQLRecoverableException

Constructs a SQLRecoverableException object with a given cause . The SQLState is initialized to null and the vendor code is initialized to 0. The reason is initialized to null if cause==null or to cause.toString() if cause!=null .

SQLRecoverableException

Constructs a SQLRecoverableException object with a given reason and cause . The SQLState is initialized to null and the vendor code is initialized to 0.

SQLRecoverableException

Constructs a SQLRecoverableException object with a given reason , SQLState and cause . The vendor code is initialized to 0.

SQLRecoverableException

Report a bug or suggest an enhancement
For further API reference and developer documentation see the Java SE Documentation, which contains more detailed, developer-targeted descriptions with conceptual overviews, definitions of terms, workarounds, and working code examples. Other versions.
Java is a trademark or registered trademark of Oracle and/or its affiliates in the US and other countries.
Copyright © 1993, 2023, Oracle and/or its affiliates, 500 Oracle Parkway, Redwood Shores, CA 94065 USA.
All rights reserved. Use is subject to license terms and the documentation redistribution policy.

Источник

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 .

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

These 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:

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

    Источник