3 Replies Latest reply on Sep 8, 2004 6:19 PM by Elias Ross

    SQLException (connection reset) from JDBC PersistenceManager

    Tero Leppänen Newbie

      Hi,

      I am using a JMS topic to notify subscribing clients of changes performed on data. I am using ObjectMessages that may contain potentially large collections of serializable transfer objects. After running the application for a while I get the following exception on the JBoss console:

      13:33:43,051 INFO [STDOUT] Caused by: org.jboss.mq.SpyJMSException: Could not store message: 127 msg=66 hard NOT_STORED NON_PERSISTENT queue=TOPIC.projectChangeTopic.ID:87.-2147483648 priority=4 lateClone=false hashCode=31425461; - nested throwable: (java.sql.SQLException: Io exception: Connection reset)
      13:33:43,051 INFO [STDOUT] at org.jboss.mq.pm.jdbc2.PersistenceManager.saveToStorage(PersistenceManager.java:1225)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.MessageCache.saveToStorage(MessageCache.java:385)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.MessageReference.makeSoft(MessageReference.java:311)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.MessageCache.validateSoftReferenceDepth(MessageCache.java:332)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.MessageCache.addInternal(MessageCache.java:142)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.MessageCache.add(MessageCache.java:112)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.JMSTopic.addMessage(JMSTopic.java:357)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:402)
      13:33:43,052 INFO [STDOUT] at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:378)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.server.JMSServerInterceptorSupport.addMessage(JMSServerInterceptorSupport.java:136)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.security.ServerSecurityInterceptor.addMessage(ServerSecurityInterceptor.java:153)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.server.TracingInterceptor.addMessage(TracingInterceptor.java:270)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:136)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:86)
      13:33:43,053 INFO [STDOUT] at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:356)
      13:33:43,054 INFO [STDOUT] at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:377)
      13:33:43,054 INFO [STDOUT] at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)
      13:33:43,054 INFO [STDOUT] ... 1 more
      13:33:43,054 INFO [STDOUT] Caused by: java.sql.SQLException: Io exception: Connection reset
      13:33:43,054 INFO [STDOUT] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
      13:33:43,054 INFO [STDOUT] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
      13:33:43,054 INFO [STDOUT] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:333)
      13:33:43,055 INFO [STDOUT] at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2061)
      13:33:43,055 INFO [STDOUT] at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
      13:33:43,055 INFO [STDOUT] at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
      13:33:43,055 INFO [STDOUT] at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
      13:33:43,055 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:56)
      13:33:43,055 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:335)
      13:33:43,055 INFO [STDOUT] at org.jboss.mq.pm.jdbc2.PersistenceManager.add(PersistenceManager.java:783)
      13:33:43,056 INFO [STDOUT] at org.jboss.mq.pm.jdbc2.PersistenceManager.saveToStorage(PersistenceManager.java:1211)
      13:33:43,056 INFO [STDOUT] ... 17 more


      The version of JBoss I use is 3.2.5. Restarting JBoss seems to help, but usually the problem returns after running a while. It might be that the bug occurs more easily when the messages sent are large. We are using Oracle database as a storage for the JDBC persistence manager. The configuration file for the persistence manager is attached below.

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

      <!-- $Id: oracle-jdbc2-service.xml,v 1.1 2004/08/18 13:41:18 kimmo Exp $ -->



      <!-- ==================================================================== -->
      <!-- Persistence and caching using Oracle -->
      <!-- IMPORTANT: Remove hsqldb-jdbc2-service.xml -->
      <!-- ==================================================================== -->

      <!--
      | The destination manager is the core service within JBossMQ
      -->

      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
      <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager
      <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager


      <!--
      | The MessageCache decides where to put JBossMQ message that
      | are sitting around waiting to be consumed by a client.
      |
      | The memory marks are in Megabytes. Once the JVM memory usage hits
      | the high memory mark, the old messages in the cache will start getting
      | stored in the DataDirectory. As memory usage gets closer to the
      | Max memory mark, the amount of message kept in the memory cache aproaches 0.
      -->

      50
      60
      jboss.mq:service=PersistenceManager


      <!-- The PersistenceManager is used to store messages to disk. -->
      <!--
      | The jdbc2 PersistenceManager is the new improved JDBC implementation.
      | This implementation allows you to control how messages are stored in
      | the database.
      |
      | This jdbc2 PM configuration has been tested against Oracle
      -->

      <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=testDB

      BLOB_TYPE=BINARYSTREAM_BLOB
      INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
      INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
      SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
      SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
      UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
      DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?
      DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
      DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
      DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
      MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER )
      CREATE_TABLES_ON_STARTUP = TRUE






      Can anyone help?

      Regards,
      Tero

        • 1. More of the problem
          Tero Leppänen Newbie


          Some additional information:

          It seems that the JMS_MESSAGES table remains empty. This probably means that the PersistenceManager does not ever get anything written to the testDB database. It is like the whole PersistenceManager is not working. Otherwise testDB datasource seems to be working fine.

          Some help would be much appreciated.

          Regards,
          Tero

          • 2. Re: SQLException (connection reset) from JDBC PersistenceMan
            Tero Leppänen Newbie


            When I turned the debug messages on for org.jboss category, I got also the following exception on the console. I wonder if this could help to pinpoint the problem:

            16:38:14,852 ERROR [TxConnectionManager] There is something wrong with the pooling?
            java.lang.IllegalStateException: afterCompletion called with wrong tx! Expected: null, actual: TransactionImpl:XidImpl [FormatId=257, GlobalId=devemine.co.helsinki.fi//404, BranchQual=]
            at org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener$TxRemover.afterCompletion(TxConnectionManager.java:635)
            at org.jboss.tm.TransactionImpl.doAfterCompletion(TransactionImpl.java:1398)
            at org.jboss.tm.TransactionImpl.rollback(TransactionImpl.java:463)
            at org.jboss.tm.TxManager.rollback(TxManager.java:265)
            at org.jboss.mq.pm.jdbc2.PersistenceManager$TransactionManagerStrategy.endTX(PersistenceManager.java:174)
            at org.jboss.mq.pm.jdbc2.PersistenceManager.saveToStorage(PersistenceManager.java:1236)
            at org.jboss.mq.server.MessageCache.saveToStorage(MessageCache.java:385)
            at org.jboss.mq.server.MessageReference.makeSoft(MessageReference.java:311)
            at org.jboss.mq.server.MessageCache.validateSoftReferenceDepth(MessageCache.java:332)
            at org.jboss.mq.server.MessageCache.addInternal(MessageCache.java:142)
            at org.jboss.mq.server.MessageCache.add(MessageCache.java:112)
            at org.jboss.mq.server.JMSTopic.addMessage(JMSTopic.java:357)
            at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:402)
            at org.jboss.mq.server.JMSDestinationManager.addMessage(JMSDestinationManager.java:378)
            at org.jboss.mq.server.JMSServerInterceptorSupport.addMessage(JMSServerInterceptorSupport.java:136)
            at org.jboss.mq.security.ServerSecurityInterceptor.addMessage(ServerSecurityInterceptor.java:153)
            at org.jboss.mq.server.TracingInterceptor.addMessage(TracingInterceptor.java:270)
            at org.jboss.mq.server.JMSServerInvoker.addMessage(JMSServerInvoker.java:136)
            at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:86)
            at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:356)
            at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:377)
            at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)
            at java.lang.Thread.run(Thread.java:534)




            Regards,
            Tero

            • 3. Re: SQLException (connection reset) from JDBC PersistenceMan
              Elias Ross Master


              Use the Oracle OCI driver with JMS, should be mentioned in FAQ, since THIN can't do blobs over 4k. Also, if your DB goes up/down (or net does) you might get this.