This article discusses the causes and the solution for the error “SQLException: This connection has been closed“. that can happen when using a Java Connection to the Database that is no longer active.
Problem Description
When you execute the close method on a java.sql.Connection object, that will release this Connection object’s database and JDBC resources immediately. Therefore, if you try to use this Connection again it will result in the following error:
Caused by: org.postgresql.util.PSQLException: This connection has been closed. at [email protected]//org.postgresql.jdbc.PgConnection.checkClosed(PgConnection.java:782) at [email protected]//org.postgresql.jdbc.PgConnection.prepareStatement(PgConnection.java:1684) at [email protected]/
Sometimes the error is a bit different, more generic like the following one:
Caused by: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2497) at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2233) at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:310) at [email protected]//org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446)
Let’s see how to solve the issue in the following section.
Solving “This connection has been closed Error”
Firstly, we assume that you are not causing this error by calling the method close() directly in your code. The trivial solution would be to close properly connections as you can read from this article : Using try-with-resources to close database connections
Therefore, we assume you are using Connection from a Pool that an application server manages. Let’s see two common cases, that is WildFly manages the connection pool or Tomcat Web server manages the Pool. In line of principle, you can use the same solution also for other Application Server
WildFly Managing the Connection Pool
WildFly uses a Connection Pool to provide Database Connection and wraps them in a DataSource object. Each time you close a Connection from the Datasource it returns to the Pool. However, if you don’t use the connection for more than the the idle-timeout-minutes, the DataSource’s idleRemover will destroy the idle connection.
This can result in the number of physical connections being reduced to 0 or the min-pool-size.
Therefore, the first thing to verify is that the idle-timeout-minutes property is greater than 0 but less than the timeout period specified on the database server, network firewalls, etc. to allow graceful termination by WildFly.
In the above snapshot, the idle-timeout-minutes is set to 5 ,minutes. If this value is greater than the Database timeout ( or possibly firewall or network timeouts) , then you should reduce the idle-timeout-minutes property accordingly.
Another possible reason why you are seeing the “SQLException: This connection has been closed” is that you are not validating your Database connections. Without any validation of your Connections if, for some reasons the Connection becomes invalid then it will throw the “This connection has been closed”. This is common, for example, if you are restarting the Database but you haven’t flushed WildFly Connection Pool.
To validate a Database Connection you can apply two strategies. For example, you can activate the Background Validation of the Connections:
<validation> <check-valid-connection-sql>select 1</check-valid-connection-sql> <validate-on-match>false</validate-on-match> <background-validation>true</background-validation> <background-validation-millis>10000</background-validation-millis> </validation>
To learn more about Connection Validation you can check this article: How to validate Database connections in JBoss / WildFly
Tomcat Managing the Connection Pool
For Tomcat users, you can configure the maximum idle time with the attribute connectionTimeout of your Datasource:
<Context context="ROOT" debug="0" reloadable="false" useHttpOnly="true" cacheMaxSize="40960" cacheTTL="60000" cachingAllowed="true" antiJARLocking="true">
<Resource name="XYZ" auth="Container"
description=" DB Connection"
dataSourceClassName="org.postgresql.ds.PGSimpleDataSource"
dataSource.serverName="XXXXX"
dataSource.databaseName="XXXX"
dataSource.portNumber="XXXX"
dataSource.user="xyz"
dataSource.password="xyz"
maximumPoolSize="50"
minimumIdle="5"
connectionTimeout="300000"
factory="com.zaxxer.hikari.HikariJNDIFactory"
registerMbeans="true"
type="javax.sql.DataSource" />
Set the above to a value smaller than the network or database timeouts. If you don’t specify it, the default value is 60000 (i.e. 60 seconds)
Furthermore, if you suspect that the firewall is dropping idle connections after some time, then you should enable the socket_keepalive option. That will force TCP to send automatically TCP keepalive packets after some idle time on each connection.
Conclusion
This article as a walk through the common error “SQLException: This connection has been closed” i which happens if you or the Connection pool tries to use a Database Connection which is no longer valid.
Found the article helpful? if so please follow us on Socials
