ConfigJBossMQDB

    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.