java.sql.SQLRecoverableException:Closed Connection

Question

I am using jboss7.0.2 final with Oracle 11. Currently I am facing one issue which happens randomly. But during heavy load this error trace keeps growing. Tried so many approaches couldn't succeed.

MyDatasource Config:

<?xml version="1.0" encoding="UTF-8"?>
<datasource jndi-name="sportPool" pool-name="sportPool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
    <connection-url>jdbc:oracle:thin:@10.11.252.200:1521:sportsdb</connection-url>
    <driver>oracleDriver</driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>75</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>false</use-strict-min>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </pool>
    <security>
        <user-name>mportal</user-name>
        <password>mobile</password>
    </security>
    <timeout>
        <idle-timeout-minutes>60000</idle-timeout-minutes>
    </timeout>
</datasource>

StackTrace:

nested exception is java.sql.SQLRecoverableException: Closed Connection
        at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.translate(SQLExceptionSubclassTranslator.java:82)
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:237)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:604)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:638)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:667)
        at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:683)

        ... 50 more
Caused by: java.sql.SQLRecoverableException: Closed Connection
        at oracle.jdbc.driver.OracleStatement.ensureOpen(OracleStatement.java:4051)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3563)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
        at org.jboss.jca.adapters.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:111)
        at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
        at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:645)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:588)
        ... 56 more

npe · Accepted Answer · 2012-07-16 09:59:16Z

5

Take a look at your database settings - it's possible, that the timeout set in database is shorter than the timeout set in datasource deployment descriptor, and the database closes the connections.
Check your network - it's possible that there is something wrong with your firewall / routing settings, and the connections are closed/dropped somewhere in the middle of the route.
Take a look at this link JBoss 7 Datasource Configuration. There is a way of configuring a datasource descriptor, forcing the JBoss to check the connection when checking it out from the pool. Use the valid-connection-checker (faster) or check-valid-connection-sql (slower) setting and set the
```
<valid-connection-checker>
    org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker
</valid-connection-checker>
```
or
```
<check-valid-connection-sql>
    SELECT 1 FROM DUAL
</check-valid-connection-sql>
```

answered Jul 16, 2012 at 9:59

npe

15.8k1 gold badge59 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

satish Over a year ago

1. I don't how to check the timeout on database server. But on

satish Over a year ago

Thanks for the reply. Here are my answers to your points. 1. I don't know how to check the timeout on database server but on server i changed the idle timeout to 15mins. 2. There are no network issues as i tried so many instances. 3. I configured <check-valid-connection-sql> and restarted server, still throws same error.

npe Over a year ago

I'd also check, whether the connection is manually closed somewhere in code. If valid-connection-checker does not help, then it means, that the connection is closed after checking it out from the pool.

satish Over a year ago

So, what approach i should to fix this issue?

satish Over a year ago

A quick update for this issue. I've done following changes outside of the application - 1. Updateed ironjacamar-jdbc jar from 1.0.3 to 1.1.0 and as suggested by npe added <check-valid-connection-sql> . This solved the problem. But jboss prints warning message "IJ000612" in console which i think should be ok as long as application is getting valid connection object from jboss.

seenukarthi · Accepted Answer · 2015-03-15 10:57:53Z

A quick update on how I fixed this issue. I've done following changes outside of the application i.e in JBOSS

**ironjacamar-jdbc**

Updateed ironjacamar-jdbc jar from 1.0.3 to 1.1.0 at this location "{JBOSS_HOME_7.0.2}/modules/org/jboss/ironjacamar/jdbcadapters/main/"

Stanalone.xml

As suggested by npe added <check-valid-connection-sql>.

<datasource jndi-name="sportPool" pool-name="sptPool" enabled="true" jta="true" use-java-context="true" use-ccm="true">
    <connection-url>
        jdbc:oracle:thin:@10.11.252.200:1521:sprtsdb
    </connection-url>
    <driver>
        oracleDriver
    </driver>
    <pool>
        <min-pool-size>1</min-pool-size>
        <max-pool-size>90</max-pool-size>
        <prefill>false</prefill>
        <use-strict-min>false</use-strict-min>
        <flush-strategy>FailingConnectionOnly</flush-strategy>
    </pool>
    <security>
        <user-name>mtal</user-name>
        <password>mle</password>
    </security>
    <validation>
        <check-valid-connection-sql>SELECT 1 FROM DUAL</check-valid-connection-sql>
        <validate-on-match>false</validate-on-match>
        <background-validation>false</background-validation>
        <use-fast-fail>false</use-fast-fail>
    </validation>
    <timeout>
        <idle-timeout-minutes>15</idle-timeout-minutes>
    </timeout>
</datasource>

This solved the problem. But jboss prints warning message "IJ000612" in console which I think should be ok as long as application is getting valid connection object from jboss.

Nicholas K · Accepted Answer · 2022-04-05 09:28:16Z

1

Seems like your are losing connection, in most cases this happens when there network problems. The configuration you are using is a bit strange;

idle-timeout-minutes = 41 days , default is 15 minutes
min-pool-size = 1

probably the idle-timeout-minutes causing your SQLRecoverableExceptions, I would use a config like;

idle-timeout-minutes = 15 minutes
min-pool-size = 10
max-pool-size = 50 or even less You are using 150MB memory for 75 connections

if you don't have long blocking connection to the database 20 max pool size should be enough.

edited Apr 5, 2022 at 9:28

Nicholas K

15.4k8 gold badges35 silver badges59 bronze badges

answered Jul 16, 2012 at 9:53

Mark Bakker

1,3488 silver badges19 bronze badges

3 Comments

Mark Bakker Over a year ago

by the way the java.sql.SQLRecoverableException will probably give functional no problem to the end user

satish Over a year ago

Thanks for the reply. I tried your settings. Still running into same issue.

Mark Bakker Over a year ago

The stacktrace is caused by connection problems, in most cases this caused by bad switches, bad host files, bad infra shared production network

Collectives™ on Stack Overflow

java.sql.SQLRecoverableException:Closed Connection

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related