Java.sql.SQLRecoverableException：连接已关闭。

Question

Java.sql.SQLRecoverableException：连接已关闭。

4

我正在使用JBoss 7.0.2 Final和Oracle 11，目前我遇到了一个随机出现的问题。但是在重负载情况下，这个错误追踪会不断增长。尝试了很多方法都没有成功。

我的数据源配置：

<?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>

堆栈跟踪：

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

- satish

3个回答

3

以下是我如何解决此问题的快速更新。我进行了以下更改，不在应用程序内部进行，而是在JBOSS中进行。

**ironjacamar-jdbc**

在此位置"{JBOSS_HOME_7.0.2}/modules/org/jboss/ironjacamar/jdbcadapters/main/"更新了ironjacamar-jdbc jar从1.0.3到1.1.0。

Stanalone.xml

根据npe的建议添加了<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>

这解决了问题。但是jboss在控制台上打印了警告信息"IJ000612"，我认为只要应用程序从jboss获取到有效的连接对象，这应该是可以接受的。

- satish

1

看起来你的连接出现了问题，在大多数情况下，这是由于网络问题引起的。你正在使用的配置有点奇怪；

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

很可能是空闲超时分钟导致了您的SQLRecoverableExceptions，我建议使用以下配置;

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

如果您没有长时间阻塞数据库连接，最大池大小为20应该足够了。

- Mark Bakker

顺便说一下，java.sql.SQLRecoverableException 可能不会给最终用户带来任何功能上的问题。 - Mark Bakker

谢谢回复。我尝试了你的设置，但仍然遇到同样的问题。 - satish

堆栈跟踪是由连接问题引起的，在大多数情况下，这是由于错误的开关、错误的主机文件、错误的基础设施共享生产网络引起的。 - Mark Bakker

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- npe · Accepted Answer

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>
```