数据源不定时间段后连接中断(解决方法：连接关闭后设置自启动，重新自动连接；Jboss异常：SQL Error 17002和SQL Error 17008

来源：互联网发布：上证综合指数季度数据编辑：程序博客网时间：2024/05/02 05:46

Jboss异常：SQL Error 17002和SQL Error 17008

原系统为行业内业务系统，布署了两套系统，分别采用Jboss和webLogic作为服务器，两套系统在同一个物理机器上，使用两个端口。

服务器：weblogic12.1.2 Jboss eap6.0

数据库：oracle12(另一个物理机器，两套系统共用)

jboss系统在试运行过程中，会不定时挂掉，经查询jboss日志，当系统挂掉时，会报SQL Error 17008和SQL Error 17002的错误，如下：

[html] view plain copy
09:24:41,771 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/0.0.0.0:8089-11) SQL Error: 17008, SQLState: 99999  
09:24:41,771 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/0.0.0.0:8089-11) Closed Connection  
09:24:41,771 ERROR [org.jboss.as.ejb3.tx.CMTTxInterceptor] (http-/0.0.0.0:8089-11) javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: Closed Connection  
09:24:41,772 ERROR [org.jboss.as.ejb3.invocation] (http-/0.0.0.0:8089-11) JBAS014134: EJB Invocation failed on component JPADAOImpl for method public abstract java.util.List com.XXXX.getObjects(java.lang.String,boolean): javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: Closed Connection  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleInCallerTx(CMTTxInterceptor.java:138) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:203) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:305) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  

......

[html] view plain copy
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Closed Connection  
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1372) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1295) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:317) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  

......

[html] view plain copy
Caused by: org.hibernate.exception.GenericJDBCException: Closed Connection  
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  

......

[html] view plain copy
Caused by: java.sql.SQLException: Closed Connection  
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)  
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  

......

-----------------------------------------------------------------------------------------------------

[html] view plain copy
09:24:54,605 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/0.0.0.0:8089-7) SQL Error: 17002, SQLState: 08006  
09:24:54,605 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-/0.0.0.0:8089-7) Io exception: Connection timed out  
09:24:54,606 ERROR [org.jboss.as.ejb3.tx.CMTTxInterceptor] (http-/0.0.0.0:8089-7) javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.JDBCConnectionException: Io exception: Connection timed out  
09:24:54,606 ERROR [org.jboss.as.ejb3.invocation] (http-/0.0.0.0:8089-7) JBAS014134: EJB Invocation failed on component JPADAOImpl for method public abstract java.lang.Object com.XXXX.jpa.JPADAO.getSingleObject(java.lang.String,java.lang.Object[]): javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.JDBCConnectionException: Io exception: Connection timed out  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleInCallerTx(CMTTxInterceptor.java:138) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:203) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:305) [jboss-as-ejb3-7.1.2.Final-redhat-1.jar:7.1.2.Final-redhat-1]  

......

[html] view plain copy
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.JDBCConnectionException: Io exception: Connection timed out  
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1372) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1295) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:265) [hibernate-entitymanager-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  

......

[html] view plain copy
Caused by: org.hibernate.exception.JDBCConnectionException: Io exception: Connection timed out  
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:131) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.1.3.Final-redhat-1.jar:4.1.3.Final-redhat-1]  

......

[html] view plain copy
Caused by: java.sql.SQLRecoverableException: Io exception: Connection timed out  
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)  
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:521) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  

......

[html] view plain copy
Caused by: java.net.SocketException: Connection timed out  
    at java.net.SocketInputStream.socketRead0(Native Method) [rt.jar:1.6.0_30]  
    at java.net.SocketInputStream.read(SocketInputStream.java:129) [rt.jar:1.6.0_30]  
    at oracle.net.ns.Packet.receive(Packet.java:240) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  
    at oracle.net.ns.DataPacket.receive(DataPacket.java:92) [ojdbc14.jar:Oracle JDBC Driver version - "10.2.0.3.0"]  

......

从日志上看，是因为数据库连接关闭导致发生异常。调整数据库连接池大小、增加事务超时时间均无效果。

解决办法：

Jboss的配置文件standalone.xml：向datasources节点中的datasource子节点增加validation节点，如下

[html] view plain copy
<datasources>  
    <datasource jta="true" jndi-name="java:jboss/datasources/OracleDS" pool-name="OracleDS" enabled="true" use-java-context="true" use-ccm="true">  
        <connection-url>jdbc:oracle:thin:@IP地址:1521:orcl</connection-url>  
        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>  
        <driver>oracle</driver>  
        <pool>  
            <min-pool-size>2</min-pool-size>  
            <max-pool-size>20</max-pool-size>  
            <prefill>true</prefill>  
            <use-strict-min>false</use-strict-min>  
            <flush-strategy>FailingConnectionOnly</flush-strategy>  
        </pool>  
        <security>  
            <user-name>DBUserName</user-name>  
            <password>DBPassword</password>  
        </security>  
        <span style="color:#ff0000;"><validation>  
            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>  
            <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>  
            <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>  
        </validation></span>  
    </datasource>  
    <drivers>  
        <driver name="oracle" module="com.oracle">  
            <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>  
        </driver>  
    </drivers>  
</datasources>  

对数据库连接进行验证。

阅读全文

0 0