Changing the database used by JBossMQ

By default JBossMQ uses hsqldb for persistence and caching. From 3.2.4 it also uses it

for saving durable topic subscriptions and authentication.

Do not use hypersonic in production

hsqldb is not a production quality database. It is suitable for demos and testing. JBoss ships with the database to help you get something working out of the box.

Known problems with hsqldb:

• No transaction isolation

• Thread and socket leaks - connection.close() does not tidy up resources

• Persistence quality - the database is held in a file in the JBoss data directory, it is a common problem that log becomes corrupted after a failure, leaving you to manually edit the file to recover the database.

• Stability under load - strange problems have been seen under load, including the database process "vanishing", i.e. it stops processing when too much data is sent, usually because of an OutOfMemory problem, but not necessarily.

• hsqldb uses a file on the local server - although this can be changed to use a remote hsqldb instance, this negates many of the benefits of using hsqldb. The local file makes it impossible to use in a clustered environment.

Please go to the Don't use Hypersonic in production wiki for more information.

Approach 1: Easy, use "DefaultDS"

In this section we will be using "DefaultDS" as the jndi name for the datasource. Since all JBoss services using persistence are configured by default to use a datasource with jndi name "DefaultDs", the number of changes is reduced to a minimum.

Throughout the intructions we will be using an Oracle datasource as an

example.  You can find other example configurations for popular database systems in $JBOSS_HOME/docs/examples/jca and

$JBOSS_HOME/docs/examples/jms.

Installing the jdbc driver

JDBC drivers are provided in JAR archives by the database vendor or a third-party company.

 - Copy the jar file to server/[your server config]/lib

Deploying the datasource descriptor for your database

Datasource examples for the most popular databases are available in $JBOSS_HOME/docs/examples/jca.

 - Copy docs/examples/jca/oracle-ds.xml to deploy{-hasingleton}
 - Replace the <jndi-name> property value with "DefaultDS"
 - Configure the datasource (server url, user, password, etc...)
 - Remove deploy{-hasingleton}/hsqldb-ds.xml

Updating the Persistence Manager service

The persistence manager is using database-specific queries to create tables and other indexes.

Alternate persistence configurations can be found in $JBOSS_HOME/docs/examples/jms.

 - Copy docs/examples/jms/oracle-jdbc2-service.xml to deploy{-hasingleton}/jms
 - Remove deploy{-hasingleton}/hsqldb-jdbc2-service.xml

You may now restart the server instance.

Approach 2: Advanced, use your own datasource name

In this section, we describe the various changes required when the datasource's jndi name is NOT "DefaultDS".

Throughout the intructions we will be referencing an Oracle datasource named "OracleDS"

Installing the jdbc driver

JDBC drivers are provided in JAR archives by the database vendor or a third-party company.

 - Copy the jar file to server/[your server config]/lib

Deploying the datasource descriptor for your database

Datasource examples for the most popular databases are available in $JBOSS_HOME/docs/examples/jca.

 - Copy docs/examples/jca/oracle-ds.xml to deploy{-hasingleton}
 - Update the <jndi-name> property if needed
 - Configure the datasource (server url, user, password, etc...)
 - Remove deploy{-hasingleton}/hsqldb-ds.xml

Note: An XA datasource is not required for JBossMQ persistence. All work is done is separate transactions that only includes work on the JBossMQ tables. i.e. there is a single branch so there is no need for two phase commit.

Updating the Persistence Manager service

The persistence manager is using database-specific queries to create tables and other indexes.

Alternate persistence configurations can be found in $JBOSS_HOME/docs/examples/jms.

 - Copy docs/examples/jms/oracle-jdbc2-service.xml to deploy{-hasingleton}/jms
 - Remove deploy{-hasingleton}/hsqldb-jdbc2-service.xml

Updating the State Manager service (3.2.4+)

The state Manager holds durable subscriptions. You need to point the service at the correct datasource. In deploy{-hasingleton}/hsqldb-jdbc-state-service.xml

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

for JBoss4 this is

    <depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=OracleDS</depends>

You can also rename the config file to oracle-jdbc-state-service.xml if you like.

Changing the login config (3.2.4+)

Additionally the JBossMQ login config needs to be changed to use your chosen datasource. In

conf/login-config.xml update the module option dsJndiName.

    <!-- Security domain for JBossMQ -->
    <application-policy name = "jbossmq">
       <authentication>
          <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
             flag = "required">
             <module-option name = "unauthenticatedIdentity">guest</module-option>
             <module-option name = "dsJndiName">java:/OracleDS</module-option>
             <module-option name = "principalsQuery">SELECT PASSWD FROM JMS_USERS WHERE USERID=?</module-option>
             <module-option name = "rolesQuery">SELECT ROLEID, 'Roles' FROM JMS_ROLES WHERE USERID=?</module-option>
          </login-module>
       </authentication>
    </application-policy>

Note: If you get a JMSSecurity exception during the deployment, check out WhyDoIGetNullIsNOTAuthenticated

Updating the timer service

The Timer service is not used by JBossMQ although by removing any datasource with jndi name "DefaultDS" it will fail to deploy.

So in /deploy/ejb-deployer.xml, please update the optional attribute "Datasource" on the EJBTimerService MBean as follows:

 <!-- A persistence policy that persistes timers to a database -->
  <mbean code="org.jboss.ejb.txtimer.DatabasePersistencePolicy" name="jboss.ejb:service=EJBTimerService,persistencePolicy=database">
    <!-- DataSource JNDI name -->
    <depends optional-attribute-name="DataSource">jboss.jca:service=DataSourceBinding,name=OracleDS</depends>
    ...

Updating the HiLo generator service

The HiLo generator service is not used by JBossMQ although by removing any datasource with jndi name "DefaultDS" it will fail to deploy.

in /deploy/uuid-key-generator/META-INF, update the optional attribute "Datasource" on the KeyGeneratorFactory MBean as follows:

  <!-- HiLoKeyGeneratorFactory -->
  <mbean code="org.jboss.ejb.plugins.keygenerator.hilo.HiLoKeyGeneratorFactory"
         name="jboss:service=KeyGeneratorFactory,type=HiLo">
     <depends>jboss:service=TransactionManager</depends>
     <!-- Attributes common to HiLo factory instances -->
     <!-- DataSource JNDI name -->
     <depends optional-attribute-name="DataSource">jboss.jca:service=DataSourceBinding,name=OracleDS</depends>
     ....

Updating the juddi-service

The juddi service is not used by JBossMQ although by removing any datasource with jndi name "DefaultDS" it will fail to deploy.

Modify /deploy/juddi-service.sar/META-INF/jboss-service.xml to reflect new datasource name it depends on:

     ....
     <depends>jboss.jca:service=DataSourceBinding,name=OracleDS</depends>
     ....

Other considerations

The schema is not optimized!

As noted in the persistence configuration the schema created by JBoss is not optimized. You may want to create the schema by hand in the database. This will also include creating indexes. A commonly used access path is select ... from JMS_MESSAGES where TXID=? and TXOP=? so creating an index over TXID and TXOP improves performance.

Issues using MySQL and JBoss 4.0.3 / 4.0.3SP1

Using the example configuration for MySQL you may see the following error: -

org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: Every derived table must have its own alias)

Edit the persistence configuration and change the line :-

SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)

to: -

SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES

See http://jira.jboss.com/jira/browse/JBAS-2425

http://www.jboss.com/index.html?module=bb&op=viewtopic&p=39066723906672

Also using MySQL 4.1.10 this query was causing JBoss to hang for me, upgrading to MySQL 4.1.12 resolved this for me.

Issues using Postgres 8.1.3 and JBoss 4.0.3 / 4.0.3SP1

Using the example configuration for Postgres you may see the following error: -

org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias

Edit the persistence configuration and change the line :-

SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT M AX(TXID) AS TXID FROM JMS_MESSAGES)

to: -

SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT M AX(TXID) AS TXID FROM JMS_MESSAGES) AS TXID

See http://jira.jboss.com/jira/browse/JBAS-2956

http://www.jboss.com/index.html?module=bb&op=viewtopic&t=79227

Issues using Postgres with JBoss 4.2.x

The /docs/examples/jms/postgres-jdbc2-service.xml differs from the standard deploy{-hasingleton}/hsqldb-jdbc2-service.xml. The jboss.mq:service=DestinationManager mbean has the following extra configuration in the hsql version:

    <depends optional-attribute-name="ThreadPool">jboss.mq:service=ThreadPool</depends>
    <depends>jboss:service=Naming</depends>

Without this configuration in the Postgres file, the number of JMSThread (the actual thread name) instances spawned by the application server is not managed. When this is added to the Postgres file, the application server will not spawn more threads than set in the ThreadPool configuration.