2 Replies Latest reply on Dec 16, 2003 2:53 PM by Ray Ploski

    Oracle connections constantly dropped.

    Ray Ploski Newbie

      We are running JBoss 3.2.2 with Oracle 8.1.7.4 and Oracle's thin driver ojdbc14.jar using a pooled connection source. After almost every query, within milliseconds of one another, the connection becomes invalid. It seems to be opening and closing connections for every call. If we turn off the OracleValidConnectionChecker, we get a closed connection returned to us. Someone please take pitty and show how this can be avoided. The log shows the following stack:

      2003-12-11 11:06:33,133 WARN [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Destroying connection that is not valid, due to the following exception:

      java.sql.SQLException: pingDatabase failed status=-1

      at org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker.isValidConnection(Ljava.sql.Connection;)Ljava.sql.SQLException;(OracleValidConnectionChecker.java:48)

      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnectionFactory.isValidConnection(Ljava.sql.Connection;)Ljava.sql.SQLException;(BaseWrapperManagedConnectionFactory.java:427)

      at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkValid()Z(BaseWrapperManagedConnection.java:253)

      at org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.matchManagedConnections(Ljava.util.Set;Ljavax.security.auth.Subject;Ljavax.resource.spi.ConnectionRequestInfo;)Ljavax.resource.spi.ManagedConnection;(LocalManagedConnectionFactory.java:182)

      at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.getConnection(Ljavax.security.auth.Subject;Ljavax.resource.spi.ConnectionRequestInfo;)Lorg.jboss.resource.connectionmanager.ConnectionListener;(InternalManagedConnectionPool.java:174)

      at org.jboss.resource.connectionmanager.JBossManagedConnectionPool$BasePool.getConnection(Ljavax.security.auth.Subject;Ljavax.resource.spi.ConnectionRequestInfo;)Lorg.jboss.resource.connectionmanager.ConnectionListener;(JBossManagedConnectionPool.java:496)

      at org.jboss.resource.connectionmanager.BaseConnectionManager2.getManagedConnection(Ljavax.security.auth.Subject;Ljavax.resource.spi.ConnectionRequestInfo;)Lorg.jboss.resource.connectionmanager.ConnectionListener;(BaseConnectionManager2.java:425)

      at org.jboss.resource.connectionmanager.TxConnectionManager.getManagedConnection(Ljavax.security.auth.Subject;Ljavax.resource.spi.ConnectionRequestInfo;)Lorg.jboss.resource.connectionmanager.ConnectionListener;(TxConnectionManager.java:318)

      at org.jboss.resource.connectionmanager.BaseConnectionManager2.allocateConnection(Ljavax.resource.spi.ManagedConnectionFactory;Ljavax.resource.spi.ConnectionRequestInfo;)Ljava.lang.Object;(BaseConnectionManager2.java:477)

      at org.jboss.resource.connectionmanager.BaseConnectionManager2$ConnectionManagerProxy.allocateConnection(Ljavax.resource.spi.ManagedConnectionFactory;Ljavax.resource.spi.ConnectionRequestInfo;)Ljava.lang.Object;(BaseConnectionManager2.java:814)

      at org.jboss.resource.adapter.jdbc.WrapperDataSource.getConnection()Ljava.sql.Connection;(WrapperDataSource.java:102)

      at com.mytravel.sql.ConnectionPool.getConnection(I)Ljava.sql.Connection;(ConnectionPool.java:99)

      at com.mytravel.sql.ConnectionPool.getConnection()Ljava.sql.Connection;(ConnectionPool.java:86)

      at com.mytravel.sql.Database.executeQuery(Ljava.lang.String;I)Ljava.sql.ResultSet;(Database.java:127)

      As for the configuration, the oracle-ds is attached below.

      <?xml version="1.0" encoding="UTF-8"?>

      <local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@MYDB.prv:1522:MYTS</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>myuser</user-name>
      *****
      <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool -->
      <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
      <!-- Checks the Oracle error codes and messages for fatal errors -->
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <new-connection-sql>select sysdate from dual</new-connection-sql>
      <check-valid-connection-sql>select sysdate from dual</check-valid-connection-sql>
      </local-tx-datasource>

        • 1. Re: Oracle connections constantly dropped.
          Adrian Brock Master

          Does pingDatabase work on Oracle8?
          I've only tested it with 9i

          Regards,
          Adrian

          • 2. Re: Oracle connections constantly dropped.
            Ray Ploski Newbie

            I found the resolution to the issue. We were using OracleCachedRowSet (OCRS) in our code. There seems to be an issue with it. It fails and leaks connections on DBCP, Proxool and JBoss. We would often see connections closed and other malfunctions while under heavy load. JBoss would show roughly double the amount of connections closed than opened.

            We ran sample queries without OCRS and could sucessfully execute thousands of iterations without issue. After adding OCRS the problem manifested itself again even under one user. We have an open tar with Oracle support regarding the issue.


            By the way, yes, pingDatabase() does work on Oracle 8i