3 Replies Latest reply on Jun 29, 2006 1:39 PM by Elias Ross

    JBoss JMS Message persistance with Ingres problems

    alan Jones Newbie

      We are currently trying to get JBoss 4.0.3 working with Ingres (instead of HSQLDB) as a means of JMS message persistance. We have our demo application successfully connecting to the Ingres R3 database, and it has created the necessary tables in the database for us. However, when our demo application (the message sending app) publishes a message to a topic, on calling the TopicPublisher.publish(.. text message ..), the demo application just hangs. No error messages are produced, either by the demo app or the JBoss console window.

      The contents of our ingres-jdbc2-service.xml are as follows:

      <server>
      
       <mbean code="org.jboss.mq.server.jmx.DestinationManager" name="jboss.mq:service=DestinationManager">
       <depends optional-attribute-name="MessageCache">jboss.mq:service=MessageCache</depends>
       <depends optional-attribute-name="PersistenceManager">jboss.mq:service=PersistenceManager</depends>
       <depends optional-attribute-name="StateManager">jboss.mq:service=StateManager</depends>
       <depends>jboss:service=Naming</depends>
       </mbean>
      
       <mbean code="org.jboss.mq.server.MessageCache"
       name="jboss.mq:service=MessageCache">
       <attribute name="HighMemoryMark">50</attribute>
       <attribute name="MaxMemoryMark">60</attribute>
       <attribute name="CacheStore">jboss.mq:service=PersistenceManager</attribute>
       </mbean>
      
       <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
       name="jboss.mq:service=PersistenceManager">
       <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=IngresDS</depends>
       <attribute name="SqlProperties">
       BLOB_TYPE=BYTES_BLOB
       INSERT_TX = INSERT INTO jms_ingres_user.JMS_TRANSACTIONS (TXID) values(?)
       INSERT_MESSAGE = INSERT INTO jms_ingres_user.JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
       SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM jms_ingres_user.JMS_TRANSACTIONS
       SELECT_MAX_TX = SELECT MAX(TXID) FROM jms_ingres_user.JMS_TXIDS
       DELETE_ALL_TX = DELETE FROM jms_ingres_user.JMS_TRANSACTIONS
       SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM jms_ingres_user.JMS_MESSAGES WHERE DESTINATION=?
       SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM jms_ingres_user.JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
       MARK_MESSAGE = UPDATE jms_ingres_user.JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MESSAGE = UPDATE jms_ingres_user.JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
       UPDATE_MARKED_MESSAGES = UPDATE jms_ingres_user.JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
       UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE jms_ingres_user.JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
       DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM jms_ingres_user.JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM jms_ingres_user.JMS_TRANSACTIONS) AND TXOP=?
       DELETE_TX = DELETE FROM jms_ingres_user.JMS_TRANSACTIONS WHERE TXID = ?
       DELETE_MARKED_MESSAGES = DELETE FROM jms_ingres_user.JMS_MESSAGES WHERE TXID=? AND TXOP=?
       DELETE_TEMPORARY_MESSAGES = DELETE FROM jms_ingres_user.JMS_MESSAGES WHERE TXOP='T'
       DELETE_MESSAGE = DELETE FROM jms_ingres_user.JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
       CREATE_MESSAGE_TABLE = create table "jms_ingres_user".jms_messages (messageid integer not null, destination varchar(255) not null, txid integer, txop char(1), messageblob long byte, primary key (messageid, destination) with (structure = btree, nonleaffill = 80, leaffill = 70, fillfactor = 80, extend = 16, allocation = 4)) with noduplicates, location = (ii_database), security_audit=(table,norow), journaling
       CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX "jms_ingres_user".JMS_MESSAGES_TXOP_TXID ON "jms_ingres_user".JMS_MESSAGES (TXOP, TXID) with structure = btree, nocompression, key = (TXOP, TXID), nonleaffill = 80, leaffill = 70, fillfactor = 80, location = (ii_database)
       CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX "jms_ingres_user".JMS_MESSAGES_DESTINATION ON "jms_ingres_user".JMS_MESSAGES (DESTINATION) with structure = btree, nocompression, key = (DESTINATION), nonleaffill = 80, leaffill = 70, fillfactor = 80, location = (ii_database)
       CREATE_TX_TABLE = create table "jms_ingres_user".jms_transactions (txid integer, primary key (txid) with (structure= btree, nonleaffill = 80, leaffill = 70, fillfactor = 80, extend = 16, allocation = 4)) with noduplicates, location = (ii_database), security_audit=(table,norow), journaling
       CREATE_TABLES_ON_STARTUP = TRUE
       </attribute>
       <!-- Uncomment to override the transaction timeout for recovery per queue/subscription, in seconds -->
       <!--attribute name="RecoveryTimeout">0</attribute-->
       </mbean>
      
      </server>


      In this, we are most concerned about the values for BLOB_TYPE, and the data type for the messageblob column. We are currently using a BLOB_TYPE of BYTES_BLOB, and a datatype of 'long byte' for the messageblob column.

      We're 99% sure the ingres-ds.xml is OK, because as I say JBoss is successfully connecting to the database and creating tables.

      Any assistance you folks could provide would be very much appreciated. We do have the option of using a different DB for persistance, but as the rest of the application is based on the Ingres DB, and is backed up and has disaster recovery etc. already set up, we would really prefer to stick with Ingres if at all possible.

      If you need any more information, then please just say and I'll get it posted ASAP.

      Many thanks.