8 Replies Latest reply on Sep 25, 2003 1:34 PM by mattygiedt

    JMS PersistenceManager on SQLServer 2K

    mattygiedt Newbie

      I am having a rather hard time getting the JMS service to use the org.jboss.mq.pm.jdbc2.PersistenceManager. I'm getting the following exceptions when trying to start JBoss 3.2.1 with Java 1.4.2 using an XA DataSource for the connections:

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

      17:01:35,239 INFO [PersistenceManager] Starting
      17:01:36,761 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
      17:01:36,761 WARN [WrappedConnection] Closing a statement you left open, please do your own housekeeping
      17:01:36,831 WARN [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=sandbox//1, BranchQual=] errorCode=XAER_RMERR
      javax.transaction.xa.XAException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]xa_rollback (0) returns -4

      [snip]

      17:01:37,963 ERROR [PersistenceManager] Starting failed
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.)

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

      I have manually created the two DB tables: (blob_type=image, but why do you have to define that at the top of the SqlProperties AND in the create_message_table sql?)

      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION))

      CREATE_TX_TABLE = CREATE TABLE MS_TRANSACTIONS (TXID INTEGER)

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

      I have included my jbossmq-service.xml file, would somebody please point out what I'm doing wrong?

      TIA,
      -Matt

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



      <!-- ==================================================================== -->
      <!-- Invocation Layers -->
      <!-- ==================================================================== -->
      <!--
      | InvocationLayers are the different transport methods that can
      | be used to access the server.
      -->


      <depends optional-attribute-name="Invoker">jboss.mq:service=Invoker
      java:/ConnectionFactory
      java:/XAConnectionFactory
      0


      <!-- ==================================================================== -->
      <!-- JBossMQ Interceptor chain configuration -->
      <!-- ==================================================================== -->
      <!-- To tune performance, you can have the Invoker skip over the TracingInterceptor -->
      <!-- and/or the SecurityManager, but then you loose the ability to trace and/or enforce security. -->

      <depends optional-attribute-name="NextInterceptor">jboss.mq:service=DestinationManager


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

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

      <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache
      <depends optional-attribute-name="ConnectionManager">jboss.jca:service=XATxCM,name=XAMSSQLDS

      BLOB_TYPE=IMAGE
      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=?
      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_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
      CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION))
      CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)



      <!-- ==================================================================== -->
      <!-- System Destinations -->
      <!-- ==================================================================== -->

      <!-- Dead Letter Queue -->

      <depends optional-attribute-name="DestinationManager">jboss.mq:service=DestinationManager



        • 1. Re: JMS PersistenceManager on SQLServer 2K
          Adrian Brock Master

          You don't need an XA driver for jdbc2
          Is IMAGE correct for SQLServer Blobs?

          Regards,
          Adrian

          • 2. Re: JMS PersistenceManager on SQLServer 2K
            mattygiedt Newbie

            Adrian,

            I have created a non-XA DS and am using it for the PersistenceManager. This seems to work well.


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

            The PersistenceManager now starts successfully, however does not do so cleanly:

            [MSSQLDS] Bound connection factory for resource adapter for ConnectionManager 'jboss.jca:service=LocalTxCM,name=MSSQLDS to JNDI
            [TxConnectionManager] Started
            [PersistenceManager] Starting
            [WrappedConnection] Closing a statement you left open, please do your own housekeeping
            [WrappedConnection] Closing a statement you left open, please do your own housekeeping
            [PersistenceManager] Started

            I realize that message is coming from the CachedConnectionManager but why?

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

            The server still does not start correctly, as I get the following (only) exception:

            [DLQHandler] Creating
            [DLQHandler] Initialization failed
            javax.jms.JMSException: Error creating the dlq connection: XAConnectionFactory not bound

            What's really confusing, is the JMSProviderLoader is dependent upon the XAConnectionFactory, and starts just fine:

            [JMSProviderLoader] Creating
            [JMSProviderLoader] Created
            [JMSProviderLoader] Starting
            [JMSProviderLoader] Bound adapter to java:/DefaultJMSProvider
            [JMSProviderLoader] Started

            The following is my MBean deployment summary:

            MBeans waiting for other MBeans:
            [ObjectName: jboss.mq.destination:service=Queue,name=listenerQueue
            state: CONFIGURED
            I Depend On: jboss.mq:service=DestinationManager

            Depends On Me: user:service=ListenerService
            , ObjectName: jboss.mq:service=InvocationLayer,type=JVM
            state: CONFIGURED
            I Depend On: jboss.mq:service=Invoker

            Depends On Me: user:service=ListenerService
            , ObjectName: jboss.mq:service=Invoker
            state: CONFIGURED
            I Depend On: jboss.mq:service=DestinationManager

            Depends On Me: jboss.mq:service=InvocationLayer,type=JVM
            , ObjectName: jboss.mq:service=DestinationManager
            state: CONFIGURED
            I Depend On: jboss.mq:service=PersistenceManager
            jboss.mq:service=StateManager

            Depends On Me: jboss.mq.destination:service=Queue,name=listenerQueue
            jboss.mq:service=Invoker
            jboss.mq.destination:service=Queue,name=DLQ
            user:service=ListenerService
            , ObjectName: jboss.mq.destination:service=Queue,name=DLQ
            state: CONFIGURED
            I Depend On: jboss.mq:service=DestinationManager

            Depends On Me: , ObjectName: user:service=ListenerService
            state: CONFIGURED
            I Depend On: jboss.mq:service=DestinationManager
            jboss.mq:service=InvocationLayer,type=JVM
            jboss.mq.destination:service=Queue,name=listenerQueue

            Depends On Me: ]

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

            Finally, is the BLOB type supposed to be NTEXT? Put differently, where can I find what that value is supposed to be, seeing as it's not in the paid documentation...

            TIA,
            -Matt

            • 3. Re: JMS PersistenceManager on SQLServer 2K
              Adrian Brock Master

              The warnings have been fixed and don't cause any problems.

              Have you fixed the "RecursiveSearch"=true in
              conf/jboss-service.xml
              A known issue with the 3.2.1 release and answered many
              times in this forum!

              I have no idea what your last question means?

              Regards,
              Adrian

              • 4. Re: JMS PersistenceManager on SQLServer 2K
                mattygiedt Newbie

                Yes. My URLDeploymentScanner contains the following attribute:

                True

                So that's not the problem. I had JMS working with an MDB until trying to move to the JDBC PersistenceManager...

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

                The last question, is in reference to your question in your first post back to me... I can't tell if its rhetorical or if you really don't know what the blob type should be for SQL Server 2K. (Seeing as you know everything, I figured rhetorical. My apologies if this is not the case.)

                TIA,
                -Matt

                • 5. Re: JMS PersistenceManager on SQLServer 2K
                  Adrian Brock Master

                  Go back in the log to where it started JMS, you should
                  see an exception saying why it cannot start.

                  For the last question, I have forgotton more than
                  I ever knew, which wasn't much when it came to SQLServer :-)

                  Regards,
                  Adrian

                  • 6. Re: JMS PersistenceManager on SQLServer 2K
                    mattygiedt Newbie

                    Attached is the server.log file. Again, the first exception is trying to load the DLQHandler.

                    • 7. Re: JMS PersistenceManager on SQLServer 2K
                      Adrian Brock Master

                      You have the MessageCache as an attribute on the PersistenceManager.

                      jdbc2 implements the cache store so the dependency should
                      be the other way around.
                      jboss.mq:service=PersistenceManager

                      Regards,
                      Adrian

                      • 8. Re: JMS PersistenceManager on SQLServer 2K
                        mattygiedt Newbie

                        The problem I was having was that my DestinationManager was dependent upon a StateManager, which wasn't present in the jbossmq-service.xml file.

                        Adding


                        <!-- This file is pulled from the configuration URL of the server -->
                        jbossmq-state.xml


                        fixed the problem.

                        I'm going to test my implementation using blob-type of NTEXT and will post a working SqlProperties for SQL Server 2000 when I'm confident that it works.

                        Thanks for the help.
                        -Matt