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

score 5 · Answer 1 · answered Jul 16 '12 at 09:59

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 '12 at 09:59

npe

15,395
1
56
55

1. I don't how to check the timeout on database server. But on – satish Jul 16 '12 at 12:05
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 and restarted server, still throws same error. – satish Jul 16 '12 at 12:07
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. – npe Jul 16 '12 at 14:49
So, what approach i should to fix this issue? – satish Jul 16 '12 at 14:57
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 . 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. – satish Jul 17 '12 at 11:16

score 3 · Accepted Answer · edited Mar 15 '15 at 10:57

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.

score 1 · Answer 3 · edited Apr 05 '22 at 09:28

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 05 '22 at 09:28

Nicholas K

15,148
7
31
57

answered Jul 16 '12 at 09:53

Mark Bakker

1,278
8
19

by the way the java.sql.SQLRecoverableException will probably give functional no problem to the end user – Mark Bakker Jul 16 '12 at 09:58
Thanks for the reply. I tried your settings. Still running into same issue. – satish Jul 16 '12 at 12:14
The stacktrace is caused by connection problems, in most cases this caused by bad switches, bad host files, bad infra shared production network – Mark Bakker Jul 16 '12 at 13:30

java.sql.SQLRecoverableException:Closed Connection

3 Answers3