JBossMQ JDBC3 Persistence Configuration

This service persists messages to the database and also implements the CacheStore

The configurations can be found in docs/examples/jms/

It is recommended you change to a real database for production.

Alternates for other databases can be found in docs/examples/jms.

You also need to deploy the relevent datasource for your chosen database.

NOTE: This configuration is experimental, it is designed to store only one copy of a message for topics.

This persistence manager is known to break under load, you MUST not use in production.

Use the JDBC2 pm instead

Removal from code base

This persistence manager has been removed from the codebase as of 3.2.8/4.0.4. The source code and example configuration can be found attached to this page as an example of how to write a persistence manager for JBossMQ

See the related JIRA issue

Example Configuration

  <mbean code="org.jboss.mq.pm.jdbc3.PersistenceManager"
      name="jboss.mq:service=PersistenceManager">
    <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=DefaultDS</depends>
    <attribute name="SqlProperties">
      BLOB_TYPE=OBJECT_BLOB
      UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGE_LOG SET TXID=?, TXOP=? WHERE TXOP=?
      UPDATE_MARKED_REFERENCES = UPDATE JMS_REFERENCE_LOG SET TXID=?, TXOP=? WHERE TXOP=?
      UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGE_LOG SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
      UPDATE_MARKED_REFERENCES_WITH_TX = UPDATE JMS_REFERENCE_LOG SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
      DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGE_LOG WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTION_LOG) AND TXOP=?
      DELETE_MARKED_REFERENCES_WITH_TX = DELETE FROM JMS_REFERENCE_LOG WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTION_LOG) AND TXOP=?
      DELETE_TX = DELETE FROM JMS_TRANSACTION_LOG WHERE TXID = ?
      DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGE_LOG WHERE TXID=? AND TXOP=?
      DELETE_MARKED_REFERENCES = DELETE FROM JMS_REFERENCE_LOG WHERE TXID=? AND TXOP=?
      INSERT_TX = INSERT INTO JMS_TRANSACTION_LOG (TXID) values(?)
      SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_TRANSACTION_LOG
      SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGE_LOG WHERE DESTINATION=?
      SELECT_REFERENCES_IN_DEST = SELECT R.MESSAGEID, M.MESSAGEBLOB, R.REDELIVERED, R.REDELIVERS FROM JMS_REFERENCE_LOG AS R, JMS_MESSAGE_LOG AS M \
                                  WHERE R.MESSAGEID = M.MESSAGEID AND R.DESTINATION=?
      SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGE_LOG WHERE MESSAGEID=? AND DESTINATION=?
      INSERT_MESSAGE = INSERT INTO JMS_MESSAGE_LOG (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP, LATECLONE) VALUES(?,?,?,?,?,?)
      INSERT_REFERENCE = INSERT INTO JMS_REFERENCE_LOG (MESSAGEID, DESTINATION, TXID, TXOP, REDELIVERED, REDELIVERS) VALUES(?,?,?,?,?,?)
      MARK_MESSAGE = UPDATE JMS_MESSAGE_LOG SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
      MARK_REFERENCE = UPDATE JMS_REFERENCE_LOG SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
      DELETE_MESSAGE = DELETE FROM JMS_MESSAGE_LOG WHERE MESSAGEID=? AND DESTINATION=?
      DELETE_REFERENCE = DELETE FROM JMS_REFERENCE_LOG WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_MESSAGE = UPDATE JMS_MESSAGE_LOG SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
      UPDATE_REFERENCE = UPDATE JMS_REFERENCE_LOG SET REDELIVERED=?, REDELIVERS=? WHERE MESSAGEID=? AND DESTINATION=?
      DELETE_ORPHANED_MESSAGES = DELETE FROM JMS_MESSAGE_LOG WHERE LATECLONE = '1' AND MESSAGEID NOT IN (SELECT MESSAGEID FROM JMS_REFERENCE_LOG)
      DELETE_ALL_TXS = DELETE FROM JMS_TRANSACTION_LOG
      CREATE_REFERENCE_TABLE = CREATE CACHED TABLE JMS_REFERENCE_LOG ( MESSAGEID INTEGER NOT NULL, \
         DESTINATION VARCHAR(256) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
         REDELIVERED CHAR(1), REDELIVERS INTEGER, \
         PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_MESSAGE_TABLE = CREATE CACHED TABLE JMS_MESSAGE_LOG ( MESSAGEID INTEGER NOT NULL, \
         DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), LATECLONE CHAR(1), \
         MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )
      CREATE_TX_TABLE = CREATE CACHED TABLE JMS_TRANSACTION_LOG ( TXID INTEGER, PRIMARY KEY (TXID) )
      CREATE_TABLES_ON_STARTUP = TRUE
    </attribute>
  </mbean>

Configurable Attributes

• ConnectionManager - the object name of the ConnectionManager controlling the DataSource

• ConnectionRetryAttempts - the number of times to retry to get a database connection at startup with a wait of 1.5 seconds in between - this is hack to workaround a problem with hsqldb not being immediately available

• SQLProperties - the sql statements used to implement the service

• GCPeriodSecs - a background thread checks for messages in JMS_MESSAGE_LOG that are no longer referenced in JMS_REFERENCE_LOG for topics

SQL Properties

*NOTE: The default schemas created by this configuration are NOT optimized.

You will want to create indexes and other configuration inside the database which may require you to create the tables by hand*

• CREATE_TABLES_ON_STARTUP - whether to create tables during the boot process if they don't exist

• BLOB_TYPE - which sql statement to use

BLOB Types

This controls which SQL statement to use, this must be matched with a suitable definition of MESSAGEBLOB in CREATE_MESSAGE_TABLE

• OBJECT_BLOB - Statement.setObject()

• BYTES_BLOB - Statement.setBytes()

• BINARYSTREAM_BLOB - Statement.setBinaryStream()

JMS_REFERENCE_LOG

Stores the list of messages in a topic - not used for queues

• MESSAGEID - the jboss internal message id

• DESTINATION - the jboss internal destination name (can include Topic subscription name)

• TXID - any transaction id associated with this message

• TXOP - the operation associated with message

• MESSAGEBLOB - the message

• REDELIVERED - whether the message has been redelivered

JMS_MESSAGE_LOG

Stores real messages in a topic and all the informatio for queues

• MESSAGEID - the jboss internal message id

• DESTINATION - the jboss internal destination name (can include Topic subscription name)

• TXID - any transaction id associated with this message

• TXOP - the operation associated with message

• MESSAGEBLOB - the message

Operations include

• A - Message added

• D - Message deleted

• T - Message added using the CacheStore interface

JMS_TRANSACTION_LOG

Stores outstanding transactions

• TXID - the transaction id

Configuration file for MySql

See in attachment: mysql-jdbc3-service.xml for MySQL 4.0.x and JBoss 4.0.1 (InnoDB tables).