1 2 3 4 Previous Next 49 Replies Latest reply on Jan 22, 2009 3:16 AM by Daniel Bevenius

    How to switch to MySQL?

    Haagen Hasle Novice

      Hi!

      I'd like to switch from Hypersonic to MySQL, but I'm having some problems.. I'd like to use MySQL for everything the ESB uses a database for; the registry, the JMS-store and anything else I don't know about yet. And not just my own .esb-file, but the entire ESB should use MySQL.

      I've found a whole lot of files that in one way or another references hsqldb, and I'm trying to edit them. But it get's a little overwhelming for me.. when I start up the server it doesn't work properly.

      If anyone can provide some assistance I would be grateful!

      Here are the files I've found and tried to edit:
      * deploy/hsqldb-ds.xml
      I see that the DefaultDS is configured here. I've replaced this file with a "mysqldb-ds.xml" that I've found (end edited). There are also some MBean-declarations in the hsqldb-ds.xml, what am I supposed to do with them?

      * deploy/jbossesb.sar/juddi-ds.xml
      I've got the same problem here, there are some MBean-declarations that I don't know what to do with. Other than that, I'm editing the values to use mysql instead of hsqldb

      * deploy/jbossesb.sar/jbossesb-properties.xml
      As far as I could see I didn't need to change anything here, cause the ESB doesn't use the standalone connetion pool settings, but the JBossESBDS?

      * deploy/jbossesb.sar/META-INF/jboss-service.xml
      Here I'm chaing to use the right scripts.

      * deploy/jbossesb.esb/message-store-ds.xml
      This is where JBossESBDS is defined. I'm editing this, but I'm don't know how to handle the depends-declarations here, and the MBean-declaration.

      * deploy/jbossesb.esb/jbossesb-service.xml
      Here I'm chaing to use the right scripts.

      * deploy/jboss-messaging.sar/hsqldb-persistence-service.xml
      Although the name here implies that this file i hsqldb-specific, I couldn't find anything inside of it that refers to hsqldb.. So I haven't done anything with it.

      I did find a "mysql-jdbc2-service.xml" in the docs, but I wasn't sure what to do with it. And it seems to use the old JMS-implementation as well, so I guess it wouldn't help me much..

      Regards, Haagen

        • 1. Re: How to switch to MySQL?
          Daniel Bevenius Master

          Hey Haagen,

          We are running postgres but I might be able to help out with some of this...

          * Replace deploy/hsqldb-ds.xml -> build/jbossesb-server-4.2.1GA/docs/examples/jca/mysql-ds.xml
          and modify it to meet your needs. Connection parameters and such. Make sure the name of the DS is the same though

          * Use the same DS example above to replace deploy/jbossesb.sar/juddi-ds.xml. Again make sure the DS name is the same.

          * deploy/jbossesb.sar/esb.uddi.properties, verify that it has a section that looks like this:

          juddi.isUseDataSource=true
          
          # jUDDI DataSource to use
          juddi.dataSource=java:/juddiDB
          


          * deploy/jbossesb.sar/META-INF/jboss-service.xml should be updated with mysql:
          <attribute name="SqlFiles">
           message-store-sql/mysql/create_database.sql
           </attribute>
          


          * deploy/jbossesb.esb/message-store-ds.xml replace this just like you did above for the other DS configurations.

          Next is JBoss Messaging:
          * replace deploy/jboss-messaging.sar/hsqldb-persistence-service.xml with this:
          <?xml version="1.0" encoding="UTF-8"?>
          <server>
           <!-- Persistence Manager MBean configuration ======================================== -->
           <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
          
           name="jboss.messaging:service=PersistenceManager"
          
           xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
          <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
           <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
           <!-- The datasource to use for the persistence manager -->
          
           <attribute name="DataSource">java:/DefaultDS</attribute>
           <!-- If true will attempt to create tables and indexes on every start-up -->
          
          
          
           <attribute name="CreateTablesOnStartup">true</attribute>
          
          
          
           <!-- If true then will use JDBC batch updates -->
          
          
          
           <attribute name="UsingBatchUpdates">true</attribute>
          
          
          
           <attribute name="SqlProperties"><![CDATA[
          
           CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY))
          
           CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (CHANNEL_ID BIGINT, MESSAGE_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INT, SCHED_DELIVERY BIGINT, PRIMARY KEY(CHANNEL_ID, MESSAGE_ID))
          
           CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID)
          
           CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JBM_MSG_REF_ORD ON JBM_MSG_REF (ORD)
          
           CREATE_IDX_MESSAGE_REF_PAGE_ORD=CREATE INDEX JBM_MSG_REF_PAGE_ORD ON JBM_MSG_REF (PAGE_ORD)
          
           CREATE_IDX_MESSAGE_REF_MESSAGE_ID=CREATE INDEX JBM_MSG_REF_MESSAGE_ID ON JBM_MSG_REF (MESSAGE_ID)
          
           CREATE_IDX_MESSAGE_REF_SCHED_DELIVERY=CREATE INDEX JBM_MSG_REF_SCHED_DELIVERY ON JBM_MSG_REF (SCHED_DELIVERY)
          
           CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, HEADERS IMAGE, PAYLOAD IMAGE, TYPE TINYINT, PRIMARY KEY (MESSAGE_ID))
          
           CREATE_IDX_MESSAGE_TIMESTAMP=CREATE INDEX JBM_MSG_REF_TIMESTAMP ON JBM_MSG (TIMESTAMP)
          
           CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID BIGINT, BRANCH_QUAL VARBINARY(254), FORMAT_ID INT, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID))
          
           CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME))
          
           INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
          
           INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          
           DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
          
           UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
          
           UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
          
           COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
          
           COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
          
           ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
          
           ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
          
           LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
          
           LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
          
           LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
          
           UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
          
           SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
          
           SELECT_EXISTS_REF_MESSAGE_ID=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE MESSAGE_ID = ?
          
           UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE CHANNEL_ID = ? AND MESSAGE_ID = ?
          
           UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
          
           LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
          
           INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          
           INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE) SELECT ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
          
           UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
          
           MESSAGE_ID_COLUMN=MESSAGE_ID
          
           REAP_MESSAGES=DELETE FROM JBM_MSG WHERE TIMESTAMP < ? AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = JBM_MSG.MESSAGE_ID)
          
           INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
          
           DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
          
           SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
          
           SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
          
           SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
          
           UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
          
           SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WITH (HOLDLOCK, ROWLOCK) WHERE NAME=?
          
           INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
          
           SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
          
           UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
          
           DUPLICATE_KEY_STATE=23000
          
           ]]></attribute>
          
          
          
           <!-- The maximum number of parameters to include in a prepared statement -->
          
          
          
           <attribute name="MaxParams">500</attribute>
          
          
          
           <attribute name="ReaperPeriod">5000</attribute>
          
           </mbean>
          
          
          
           <!-- Messaging Post Office MBean configuration
          
           ========================================= -->
          
          
          
           <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
          
           name="jboss.messaging:service=PostOffice"
          
           xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">
          
          
          
           <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
          
          
          
           <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
          
          
          
           <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
          
          
          
           <!-- The name of the post office -->
          
          
          
           <attribute name="PostOfficeName">JMS post office</attribute>
          
          
          
           <!-- The datasource used by the post office to access it's binding information -->
          
          
          
           <attribute name="DataSource">java:/DefaultDS</attribute>
          
          
          
           <!-- If true will attempt to create tables and indexes on every start-up -->
          
          
          
           <attribute name="CreateTablesOnStartup">true</attribute>
          
          
          
           <attribute name="SqlProperties"><![CDATA[
          
          CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID SMALLINT, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID INTEGER, CLSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME))
          
          INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          
          DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
          
          LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
          
           ]]></attribute>
          
          
          
           <!-- This post office is clustered. If you don't want a clustered post office then set to false -->
          
          
          
           <attribute name="Clustered">true</attribute>
          
          
          
           <!-- All the remaining properties only have to be specified if the post office is clustered.
          
           You can safely comment them out if your post office is non clustered -->
          
          
          
           <!-- The JGroups group name that the post office will use -->
          
          
          
           <attribute name="GroupName">MessagingPostOffice</attribute>
          
          
          
           <!-- Max time to wait for state to arrive when the post office joins the cluster -->
          
          
          
           <attribute name="StateTimeout">5000</attribute>
          
          
          
           <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
          
          
          
           <attribute name="CastTimeout">5000</attribute>
          
          
          
           <!-- Enable this when the JGroups multiplexer comes of age
          
           <attribute name="ChannelFactoryName">jgroups.mux:name=Multiplexer</attribute>
          
           <attribute name="ControlChannelName">udp-sync</attribute>
          
           <attribute name="DataChannelName">udp</attribute>
          
           <attribute name="ChannelPartitionName">${jboss.partition.name:DefaultPartition}-JMS</attribute>
          
           -->
          
          
          
           <!-- JGroups stack configuration for the data channel - used for sending data across the cluster -->
          
          
          
           <attribute name="DataChannelConfig">
          
           <config>
          
           <UDP
          
           mcast_addr="228.8.8.8"
          
           mcast_port="45567"
          
           tos="8"
          
           ucast_recv_buf_size="20000000"
          
           ucast_send_buf_size="640000"
          
           mcast_recv_buf_size="25000000"
          
           mcast_send_buf_size="640000"
          
           loopback="false"
          
           discard_incompatible_packets="true"
          
           max_bundle_size="64000"
          
           max_bundle_timeout="30"
          
           use_incoming_packet_handler="true"
          
           use_outgoing_packet_handler="false"
          
           ip_ttl="2"
          
           down_thread="false" up_thread="false"
          
           enable_bundling="false"/>
          
           <PING timeout="2000" down_thread="false" num_initial_members="3" up_thread="false"/>
          
           <MERGE2 max_interval="100000" down_thread="false" min_interval="20000" up_thread="false"/>
          
           <FD_SOCK down_thread="false" up_thread="false"/>
          
           <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/>
          
           <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
          
           <pbcast.NAKACK
          
           max_xmit_size="60000"
          
           use_mcast_xmit="false"
          
           gc_lag="0"
          
           retransmit_timeout="300,600,1200,2400,4800"
          
           down_thread="false" up_thread="false"
          
           discard_delivered_msgs="true"/>
          
           <UNICAST timeout="300,600,1200,2400,3600"
          
           down_thread="false" up_thread="false"/>
          
           <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
          
           down_thread="false" up_thread="false"
          
           max_bytes="400000"/>
          
           <pbcast.GMS print_local_addr="true" join_timeout="3000" use_flush="true" flush_timeout="3000"
          
           down_thread="false" up_thread="false"
          
           join_retry_timeout="2000" shun="false"
          
           view_bundling="true"
          
           view_ack_collection_timeout="5000"/>
          
           <FC max_credits="2000000" down_thread="false" up_thread="false"
          
           min_threshold="0.10"/>
          
           <FRAG2 frag_size="60000" down_thread="false" up_thread="false"/>
          
           </config>
          
           </attribute>
          
          
          
           <!-- JGroups stack configuration to use for the control channel - used for control messages -->
          
          
          
           <attribute name="ControlChannelConfig">
          
           <config>
          
           <UDP
          
           mcast_addr="228.8.8.8"
          
           mcast_port="45568"
          
           tos="8"
          
           ucast_recv_buf_size="20000000"
          
           ucast_send_buf_size="640000"
          
           mcast_recv_buf_size="25000000"
          
           mcast_send_buf_size="640000"
          
           loopback="false"
          
           discard_incompatible_packets="true"
          
           max_bundle_size="64000"
          
           max_bundle_timeout="30"
          
           use_incoming_packet_handler="true"
          
           use_outgoing_packet_handler="false"
          
           ip_ttl="2"
          
           down_thread="false" up_thread="false"
          
           enable_bundling="false"/>
          
           <PING timeout="2000"
          
           down_thread="false" up_thread="false" num_initial_members="3"/>
          
           <MERGE2 max_interval="100000"
          
           down_thread="false" up_thread="false" min_interval="20000"/>
          
           <FD_SOCK down_thread="false" up_thread="false"/>
          
           <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/>
          
           <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
          
           <pbcast.NAKACK max_xmit_size="60000"
          
           use_mcast_xmit="false" gc_lag="0"
          
           retransmit_timeout="300,600,1200,2400,4800"
          
           down_thread="false" up_thread="false"
          
           discard_delivered_msgs="true"/>
          
           <UNICAST timeout="300,600,1200,2400,3600"
          
           down_thread="false" up_thread="false"/>
          
           <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
          
           down_thread="false" up_thread="false"
          
           max_bytes="400000"/>
          
           <pbcast.GMS print_local_addr="true" join_timeout="3000" use_flush="true" flush_timeout="3000"
          
           down_thread="false" up_thread="false"
          
           join_retry_timeout="2000" shun="false"
          
           view_bundling="true"/>
          
           <FRAG2 frag_size="60000" down_thread="false" up_thread="false"/>
          
           <pbcast.STATE_TRANSFER down_thread="false" up_thread="false" use_flush="true" flush_timeout="3000"/>
          
           <pbcast.FLUSH down_thread="false" up_thread="false" timeout="20000" auto_flush_conf="false"/>
          
           </config>
          
           </attribute>
          
           </mbean>
          
          
          
           <!-- Messaging JMS User Manager MBean config
          
           ======================================= -->
          
          
          
           <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
          
           name="jboss.messaging:service=JMSUserManager"
          
           xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
          
          
          
           <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
          
          
          
           <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
          
          
          
           <attribute name="DataSource">java:/DefaultDS</attribute>
          
          
          
           <attribute name="CreateTablesOnStartup">true</attribute>
          
          
          
           <attribute name="SqlProperties"><![CDATA[
          
          CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID))
          
          CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID))
          
          SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
          
          POPULATE.TABLES.1=INSERT INTO JBM_USER (USER_ID,PASSWD,CLIENTID) VALUES ('dilbert','dogbert','dilbert-id')
          
           ]]></attribute>
          
           </mbean>
          
          
          
          </server>
          
          

          Sorry about the massive section above, just thought it would save you going hunting for the file msql-persistence-service.xml for the JBoss Messaging project.

          Hope this helps.

          Regards,

          Daniel

          • 2. Re: How to switch to MySQL?
            Daniel Bevenius Master

            I missed this in jbossesb.sar/esb.uddi.properties file:

            # jUDDI database creation
            juddi.isCreateDatabase=true
            juddi.databaseExistsSql=select * from BUSINESS_ENTITY
            juddi.sqlFiles=juddi-sql/mysq/create_database.sql,juddi-sql/mysq/import.sql
            


            /Daniel

            • 3. Re: How to switch to MySQL?
              Kevin Conner Master

              Hiya Daniel.

              Thanks very much for adding this.

              The only comment I would like to make is that the jUDDI configuration in the 4.2.1 release is now XML based and is called esb.juddi.xml. This allows us to process the configuration using the ServiceBindingManager, enabling the support of alternate settings.

              Thanks again Daniel.

              Kev

              • 4. Re: How to switch to MySQL?
                Haagen Hasle Novice

                Hi Daniel!

                Thanks for your help! I've made the changes you describe, but I get some errors at startup that seem to come because of "deploy/jboss-messaging.sar/hsqldb-persistence-service.xml". Are the content you wrote really for MySQL? Does it require any special version of MySQL (I downloaded the latest, but there are several options involved in configuring MySQL, with or without InnoDB etc..)

                Regards, Haagen

                • 5. Re: How to switch to MySQL?
                  Haagen Hasle Novice

                  I think I must have missed something as well, cause I get some errormessages when I start the server that indicates that it is trying to run the localDB.script-files in the \server\default\data\hypersonic-directory....

                  I've also tried to edit the files in the deploy/jbpm.esb-directory, there were some hsqldb-references there as well. I'm not actually using JBpm, so I guess I might as well just delete the entire directory?

                  Regards, Haagen

                  • 6. Re: How to switch to MySQL?
                    Daniel Bevenius Master

                    Hi Haagen,

                    I've updated to the latest JBM and below is the lastest mysql-persistence-service.xml.

                    So, this file should be named mysql-persistence-service.xml and contain this:

                    <?xml version="1.0" encoding="UTF-8"?>
                    
                    <!--
                     MySql persistence deployment descriptor.
                    
                     Tested with MySQL 4.1.22
                    
                     $Id$
                     -->
                    
                    <server>
                    
                     <!-- Persistence Manager MBean configuration
                     ======================================== -->
                    
                     <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
                     name="jboss.messaging:service=PersistenceManager"
                     xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
                    
                     <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                    
                     <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                    
                     <!-- The datasource to use for the persistence manager -->
                    
                     <attribute name="DataSource">java:/DefaultDS</attribute>
                    
                     <!-- If true will attempt to create tables and indexes on every start-up -->
                    
                     <attribute name="CreateTablesOnStartup">true</attribute>
                    
                     <!-- If true then will use JDBC batch updates -->
                    
                     <attribute name="UsingBatchUpdates">true</attribute>
                    
                     <attribute name="SqlProperties"><![CDATA[
                     CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) ENGINE = INNODB
                     CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (CHANNEL_ID BIGINT, MESSAGE_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(CHANNEL_ID, MESSAGE_ID)) ENGINE = INNODB
                     CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID)
                     CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JBM_MSG_REF_ORD ON JBM_MSG_REF (ORD)
                     CREATE_IDX_MESSAGE_REF_PAGE_ORD=CREATE INDEX JBM_MSG_REF_PAGE_ORD ON JBM_MSG_REF (PAGE_ORD)
                     CREATE_IDX_MESSAGE_REF_MESSAGE_ID=CREATE INDEX JBM_MSG_REF_MESSAGE_ID ON JBM_MSG_REF (MESSAGE_ID)
                     CREATE_IDX_MESSAGE_REF_SCHED_DELIVERY=CREATE INDEX JBM_MSG_REF_SCHED_DELIVERY ON JBM_MSG_REF (SCHED_DELIVERY)
                     CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, INS_TIME BIGINT, HEADERS MEDIUMBLOB, PAYLOAD LONGBLOB, PRIMARY KEY (MESSAGE_ID)) ENGINE = INNODB
                     CREATE_IDX_MESSAGE_TIMESTAMP=CREATE INDEX JBM_MSG_REF_TIMESTAMP ON JBM_MSG (TIMESTAMP)
                     CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID BIGINT, BRANCH_QUAL VARBINARY(254), FORMAT_ID INTEGER, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID)) ENGINE = INNODB
                     CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) ENGINE = INNODB
                     INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
                     CHECK_DUAL=SELECT 1 FROM JBM_DUAL
                     INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                     DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                     UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                     UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
                     COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
                     COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
                     ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
                     ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
                     LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
                     LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
                     LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
                     UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
                     SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
                     SELECT_EXISTS_REF_MESSAGE_ID=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE MESSAGE_ID = ?
                     UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE CHANNEL_ID = ? AND MESSAGE_ID = ?
                     UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
                     LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
                     INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                     INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME) SELECT ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                     UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
                     INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                     MESSAGE_ID_COLUMN=MESSAGE_ID
                     REAP_MESSAGES=DELETE FROM JBM_MSG WHERE INS_TIME < ? AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = JBM_MSG.MESSAGE_ID)
                     DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?)
                     INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
                     DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
                     SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
                     SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
                     SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
                     UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
                     SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WHERE NAME=? FOR UPDATE
                     INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
                     SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
                     UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
                     ]]></attribute>
                    
                     <!-- The maximum number of parameters to include in a prepared statement -->
                    
                     <attribute name="MaxParams">500</attribute>
                    
                     </mbean>
                    
                     <!-- Messaging Post Office MBean configuration
                     ========================================= -->
                    
                     <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
                     name="jboss.messaging:service=PostOffice"
                     xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">
                    
                     <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
                    
                     <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                    
                     <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                    
                     <!-- The name of the post office -->
                    
                     <attribute name="PostOfficeName">JMS post office</attribute>
                    
                     <!-- The datasource used by the post office to access it's binding information -->
                    
                     <attribute name="DataSource">java:/DefaultDS</attribute>
                    
                     <!-- If true will attempt to create tables and indexes on every start-up -->
                    
                     <attribute name="CreateTablesOnStartup">true</attribute>
                    
                     <attribute name="SqlProperties"><![CDATA[
                    CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) ENGINE = INNODB
                    INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                    DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
                    LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
                     ]]></attribute>
                    
                     <!-- This post office is clustered. If you don't want a clustered post office then set to false -->
                    
                     <attribute name="Clustered">true</attribute>
                    
                     <!-- All the remaining properties only have to be specified if the post office is clustered.
                     You can safely comment them out if your post office is non clustered -->
                    
                     <!-- The JGroups group name that the post office will use -->
                    
                     <attribute name="GroupName">${jboss.messaging.groupname:MessagingPostOffice}</attribute>
                    
                     <!-- Max time to wait for state to arrive when the post office joins the cluster -->
                    
                     <attribute name="StateTimeout">5000</attribute>
                    
                     <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
                    
                     <attribute name="CastTimeout">50000</attribute>
                    
                     <!-- JGroups stack configuration for the data channel - used for sending data across the cluster -->
                    
                     <!-- By default we use the TCP stack for data -->
                     <attribute name="DataChannelConfig">
                     <config>
                     <TCP start_port="7900"
                     loopback="true"
                     recv_buf_size="20000000"
                     send_buf_size="640000"
                     discard_incompatible_packets="true"
                     max_bundle_size="64000"
                     max_bundle_timeout="30"
                     use_incoming_packet_handler="true"
                     use_outgoing_packet_handler="false"
                     down_thread="false" up_thread="false"
                     enable_bundling="false"
                     use_send_queues="false"
                     sock_conn_timeout="300"
                     skip_suspected_members="true"/>
                     <MPING timeout="4000"
                     bind_to_all_interfaces="true"
                     mcast_addr="${jboss.messaging.datachanneludpaddress:228.6.6.6}"
                     mcast_port="${jboss.messaging.datachanneludpport:45567}"
                     ip_ttl="${jboss.messaging.ipttl:8}"
                     num_initial_members="2"
                     num_ping_requests="1"/>
                     <MERGE2 max_interval="100000"
                     down_thread="false" up_thread="false" min_interval="20000"/>
                     <FD_SOCK down_thread="false" up_thread="false"/>
                     <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
                     <pbcast.NAKACK max_xmit_size="60000"
                     use_mcast_xmit="false" gc_lag="0"
                     retransmit_timeout="300,600,1200,2400,4800"
                     down_thread="false" up_thread="false"
                     discard_delivered_msgs="true"/>
                     <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
                     down_thread="false" up_thread="false"
                     max_bytes="400000"/>
                     <pbcast.GMS print_local_addr="true" join_timeout="3000"
                     down_thread="false" up_thread="false"
                     join_retry_timeout="2000" shun="false"
                     view_bundling="true"/>
                     </config>
                     </attribute>
                    
                     <!-- JGroups stack configuration to use for the control channel - used for control messages -->
                    
                     <!-- We use udp stack for the control channel -->
                     <attribute name="ControlChannelConfig">
                     <config>
                     <UDP
                     mcast_addr="${jboss.messaging.controlchanneludpaddress:228.7.7.7}"
                     mcast_port="${jboss.messaging.controlchanneludpport:45568}"
                     tos="8"
                     ucast_recv_buf_size="20000000"
                     ucast_send_buf_size="640000"
                     mcast_recv_buf_size="25000000"
                     mcast_send_buf_size="640000"
                     loopback="false"
                     discard_incompatible_packets="true"
                     max_bundle_size="64000"
                     max_bundle_timeout="30"
                     use_incoming_packet_handler="true"
                     use_outgoing_packet_handler="false"
                     ip_ttl="${jboss.messaging.ipttl:2}"
                     down_thread="false" up_thread="false"
                     enable_bundling="false"/>
                     <PING timeout="2000"
                     down_thread="false" up_thread="false" num_initial_members="3"/>
                     <MERGE2 max_interval="100000"
                     down_thread="false" up_thread="false" min_interval="20000"/>
                     <FD_SOCK down_thread="false" up_thread="false"/>
                     <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/>
                     <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
                     <pbcast.NAKACK max_xmit_size="60000"
                     use_mcast_xmit="false" gc_lag="0"
                     retransmit_timeout="300,600,1200,2400,4800"
                     down_thread="false" up_thread="false"
                     discard_delivered_msgs="true"/>
                     <UNICAST timeout="300,600,1200,2400,3600"
                     down_thread="false" up_thread="false"/>
                     <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
                     down_thread="false" up_thread="false"
                     max_bytes="400000"/>
                     <pbcast.GMS print_local_addr="true" join_timeout="3000" use_flush="true" flush_timeout="3000"
                     down_thread="false" up_thread="false"
                     join_retry_timeout="2000" shun="false"
                     view_bundling="true"/>
                     <FRAG2 frag_size="60000" down_thread="false" up_thread="false"/>
                     <pbcast.STATE_TRANSFER down_thread="false" up_thread="false" use_flush="true" flush_timeout="3000"/>
                     <pbcast.FLUSH down_thread="false" up_thread="false" timeout="20000" auto_flush_conf="false"/>
                     </config>
                     </attribute>
                    
                     </mbean>
                    
                     <!-- Messaging JMS User Manager MBean config
                     ======================================= -->
                    
                     <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
                     name="jboss.messaging:service=JMSUserManager"
                     xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
                     <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                     <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                     <attribute name="DataSource">java:/DefaultDS</attribute>
                     <attribute name="CreateTablesOnStartup">true</attribute>
                     <attribute name="SqlProperties"><![CDATA[
                    CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID)) ENGINE = INNODB
                    CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) ENGINE = INNODB
                    SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
                    POPULATE.TABLES.1=INSERT INTO JBM_USER (USER_ID,PASSWD,CLIENTID) VALUES ('dilbert','dogbert','dilbert-id')
                     ]]></attribute>
                     </mbean>
                    
                    </server>
                    

                    You can see that the version tested against is MySQL 4.1.22.

                    Yeah, just delete jbpm.esb if you don't need it. Or update the hibernate.cfg.xml.

                    Regards,

                    Daniel

                    • 7. Re: How to switch to MySQL?
                      Haagen Hasle Novice

                      I got this at startup now:
                      No such SQL statement: DELETE_MESSAGE

                      I'll try to downgrade from MySQL 5.0 to 4.1.22 and see what happens.


                      Regards, Haagen

                      • 8. Re: How to switch to MySQL?
                        Haagen Hasle Novice

                        I'm afraid I get the same error with MySQL 4.1.22..

                        Regards, Haagen

                        • 9. Re: How to switch to MySQL?
                          Daniel Bevenius Master

                          Ah,ok. I think this has to do with with the version of JBM. As I took the file from the latest trunk this might only work with the newest jboss-messaging.jar.

                          We'll need on that matches the version of JBM that ships with the jbossesb.

                          Hold on a sec...


                          • 10. Re: How to switch to MySQL?
                            Daniel Bevenius Master

                            So, if you can download 1.4.0.GA of JBoss Messaging and use the file /src/etc/server/default/deploy/mysql-persistence-service.xml I think it should work for you :)

                            /Daniel

                            • 11. Re: How to switch to MySQL?
                              Haagen Hasle Novice

                              I only found jboss-messaging-1.4.0.SP1, so I'll try that.

                              Regards, Haagen

                              • 12. Re: How to switch to MySQL?
                                Haagen Hasle Novice

                                I edited the URL a little bit, so now I found the GA-version as well. So I'll use that one! :)

                                • 13. Re: How to switch to MySQL?
                                  Daniel Bevenius Master

                                  If you download the binary this file is located in example/config.

                                  Here's the contents of it:

                                  <?xml version="1.0" encoding="UTF-8"?>
                                  
                                  <!--
                                   MySql persistence deployment descriptor.
                                  
                                   Tested with MySQL 4.1.22
                                  
                                   $Id$
                                   -->
                                  
                                  <server>
                                  
                                   <!-- Persistence Manager MBean configuration
                                   ======================================== -->
                                  
                                   <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
                                   name="jboss.messaging:service=PersistenceManager"
                                   xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
                                  
                                   <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                                  
                                   <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                                  
                                   <!-- The datasource to use for the persistence manager -->
                                  
                                   <attribute name="DataSource">java:/DefaultDS</attribute>
                                  
                                   <!-- If true will attempt to create tables and indexes on every start-up -->
                                  
                                   <attribute name="CreateTablesOnStartup">true</attribute>
                                  
                                   <!-- If true then will use JDBC batch updates -->
                                  
                                   <attribute name="UsingBatchUpdates">true</attribute>
                                  
                                   <attribute name="SqlProperties"><![CDATA[
                                   CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) ENGINE = INNODB
                                   CREATE_MESSAGE_REFERENCE=CREATE TABLE JBM_MSG_REF (CHANNEL_ID BIGINT, MESSAGE_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(CHANNEL_ID, MESSAGE_ID)) ENGINE = INNODB
                                   CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID)
                                   CREATE_IDX_MESSAGE_REF_ORD=CREATE INDEX JBM_MSG_REF_ORD ON JBM_MSG_REF (ORD)
                                   CREATE_IDX_MESSAGE_REF_PAGE_ORD=CREATE INDEX JBM_MSG_REF_PAGE_ORD ON JBM_MSG_REF (PAGE_ORD)
                                   CREATE_IDX_MESSAGE_REF_MESSAGE_ID=CREATE INDEX JBM_MSG_REF_MESSAGE_ID ON JBM_MSG_REF (MESSAGE_ID)
                                   CREATE_IDX_MESSAGE_REF_SCHED_DELIVERY=CREATE INDEX JBM_MSG_REF_SCHED_DELIVERY ON JBM_MSG_REF (SCHED_DELIVERY)
                                   CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, INS_TIME BIGINT, HEADERS MEDIUMBLOB, PAYLOAD LONGBLOB, PRIMARY KEY (MESSAGE_ID)) ENGINE = INNODB
                                   CREATE_IDX_MESSAGE_TIMESTAMP=CREATE INDEX JBM_MSG_REF_TIMESTAMP ON JBM_MSG (TIMESTAMP)
                                   CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID BIGINT, BRANCH_QUAL VARBINARY(254), FORMAT_ID INTEGER, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID)) ENGINE = INNODB
                                   CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) ENGINE = INNODB
                                   INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
                                   CHECK_DUAL=SELECT 1 FROM JBM_DUAL
                                   INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                                   DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                                   UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
                                   UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
                                   COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
                                   COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
                                   ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
                                   ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
                                   LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
                                   LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
                                   LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
                                   UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
                                   SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
                                   SELECT_EXISTS_REF_MESSAGE_ID=SELECT MESSAGE_ID FROM JBM_MSG_REF WHERE MESSAGE_ID = ?
                                   UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE CHANNEL_ID = ? AND MESSAGE_ID = ?
                                   UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
                                   LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
                                   INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                                   INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME) SELECT ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                                   UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
                                   INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, INS_TIME, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
                                   MESSAGE_ID_COLUMN=MESSAGE_ID
                                   REAP_MESSAGES=DELETE FROM JBM_MSG WHERE INS_TIME < ? AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = JBM_MSG.MESSAGE_ID)
                                   DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?)
                                   INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
                                   DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
                                   SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
                                   SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
                                   SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
                                   UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
                                   SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WHERE NAME=? FOR UPDATE
                                   INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
                                   SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
                                   UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
                                   ]]></attribute>
                                  
                                   <!-- The maximum number of parameters to include in a prepared statement -->
                                  
                                   <attribute name="MaxParams">500</attribute>
                                  
                                   </mbean>
                                  
                                   <!-- Messaging Post Office MBean configuration
                                   ========================================= -->
                                  
                                   <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
                                   name="jboss.messaging:service=PostOffice"
                                   xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">
                                  
                                   <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
                                  
                                   <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                                  
                                   <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                                  
                                   <!-- The name of the post office -->
                                  
                                   <attribute name="PostOfficeName">JMS post office</attribute>
                                  
                                   <!-- The datasource used by the post office to access it's binding information -->
                                  
                                   <attribute name="DataSource">java:/DefaultDS</attribute>
                                  
                                   <!-- If true will attempt to create tables and indexes on every start-up -->
                                  
                                   <attribute name="CreateTablesOnStartup">true</attribute>
                                  
                                   <attribute name="SqlProperties"><![CDATA[
                                  CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) ENGINE = INNODB
                                  INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
                                  DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
                                  LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
                                   ]]></attribute>
                                  
                                   <!-- This post office is clustered. If you don't want a clustered post office then set to false -->
                                  
                                   <attribute name="Clustered">true</attribute>
                                  
                                   <!-- All the remaining properties only have to be specified if the post office is clustered.
                                   You can safely comment them out if your post office is non clustered -->
                                  
                                   <!-- The JGroups group name that the post office will use -->
                                  
                                   <attribute name="GroupName">${jboss.messaging.groupname:MessagingPostOffice}</attribute>
                                  
                                   <!-- Max time to wait for state to arrive when the post office joins the cluster -->
                                  
                                   <attribute name="StateTimeout">5000</attribute>
                                  
                                   <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
                                  
                                   <attribute name="CastTimeout">50000</attribute>
                                  
                                   <!-- JGroups stack configuration for the data channel - used for sending data across the cluster -->
                                  
                                   <!-- By default we use the TCP stack for data -->
                                   <attribute name="DataChannelConfig">
                                   <config>
                                   <TCP start_port="7900"
                                   loopback="true"
                                   recv_buf_size="20000000"
                                   send_buf_size="640000"
                                   discard_incompatible_packets="true"
                                   max_bundle_size="64000"
                                   max_bundle_timeout="30"
                                   use_incoming_packet_handler="true"
                                   use_outgoing_packet_handler="false"
                                   down_thread="false" up_thread="false"
                                   enable_bundling="false"
                                   use_send_queues="false"
                                   sock_conn_timeout="300"
                                   skip_suspected_members="true"/>
                                   <MPING timeout="4000"
                                   bind_to_all_interfaces="true"
                                   mcast_addr="${jboss.messaging.datachanneludpaddress:228.6.6.6}"
                                   mcast_port="${jboss.messaging.datachanneludpport:45567}"
                                   ip_ttl="8"
                                   num_initial_members="2"
                                   num_ping_requests="1"/>
                                   <MERGE2 max_interval="100000"
                                   down_thread="false" up_thread="false" min_interval="20000"/>
                                   <FD_SOCK down_thread="false" up_thread="false"/>
                                   <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
                                   <pbcast.NAKACK max_xmit_size="60000"
                                   use_mcast_xmit="false" gc_lag="0"
                                   retransmit_timeout="300,600,1200,2400,4800"
                                   down_thread="false" up_thread="false"
                                   discard_delivered_msgs="true"/>
                                   <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
                                   down_thread="false" up_thread="false"
                                   max_bytes="400000"/>
                                   <pbcast.GMS print_local_addr="true" join_timeout="3000"
                                   down_thread="false" up_thread="false"
                                   join_retry_timeout="2000" shun="false"
                                   view_bundling="true"/>
                                   </config>
                                   </attribute>
                                  
                                   <!-- JGroups stack configuration to use for the control channel - used for control messages -->
                                  
                                   <!-- We use udp stack for the control channel -->
                                   <attribute name="ControlChannelConfig">
                                   <config>
                                   <UDP
                                   mcast_addr="${jboss.messaging.controlchanneludpaddress:228.7.7.7}"
                                   mcast_port="${jboss.messaging.controlchanneludpport:45568}"
                                   tos="8"
                                   ucast_recv_buf_size="20000000"
                                   ucast_send_buf_size="640000"
                                   mcast_recv_buf_size="25000000"
                                   mcast_send_buf_size="640000"
                                   loopback="false"
                                   discard_incompatible_packets="true"
                                   max_bundle_size="64000"
                                   max_bundle_timeout="30"
                                   use_incoming_packet_handler="true"
                                   use_outgoing_packet_handler="false"
                                   ip_ttl="2"
                                   down_thread="false" up_thread="false"
                                   enable_bundling="false"/>
                                   <PING timeout="2000"
                                   down_thread="false" up_thread="false" num_initial_members="3"/>
                                   <MERGE2 max_interval="100000"
                                   down_thread="false" up_thread="false" min_interval="20000"/>
                                   <FD_SOCK down_thread="false" up_thread="false"/>
                                   <FD timeout="10000" max_tries="5" down_thread="false" up_thread="false" shun="true"/>
                                   <VERIFY_SUSPECT timeout="1500" down_thread="false" up_thread="false"/>
                                   <pbcast.NAKACK max_xmit_size="60000"
                                   use_mcast_xmit="false" gc_lag="0"
                                   retransmit_timeout="300,600,1200,2400,4800"
                                   down_thread="false" up_thread="false"
                                   discard_delivered_msgs="true"/>
                                   <UNICAST timeout="300,600,1200,2400,3600"
                                   down_thread="false" up_thread="false"/>
                                   <pbcast.STABLE stability_delay="1000" desired_avg_gossip="50000"
                                   down_thread="false" up_thread="false"
                                   max_bytes="400000"/>
                                   <pbcast.GMS print_local_addr="true" join_timeout="3000" use_flush="true" flush_timeout="3000"
                                   down_thread="false" up_thread="false"
                                   join_retry_timeout="2000" shun="false"
                                   view_bundling="true"/>
                                   <FRAG2 frag_size="60000" down_thread="false" up_thread="false"/>
                                   <pbcast.STATE_TRANSFER down_thread="false" up_thread="false" use_flush="true" flush_timeout="3000"/>
                                   <pbcast.FLUSH down_thread="false" up_thread="false" timeout="20000" auto_flush_conf="false"/>
                                   </config>
                                   </attribute>
                                  
                                   </mbean>
                                  
                                   <!-- Messaging JMS User Manager MBean config
                                   ======================================= -->
                                  
                                   <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
                                   name="jboss.messaging:service=JMSUserManager"
                                   xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
                                   <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
                                   <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                                   <attribute name="DataSource">java:/DefaultDS</attribute>
                                   <attribute name="CreateTablesOnStartup">true</attribute>
                                   <attribute name="SqlProperties"><![CDATA[
                                  CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID)) ENGINE = INNODB
                                  CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) ENGINE = INNODB
                                  SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
                                  POPULATE.TABLES.1=INSERT INTO JBM_USER (USER_ID,PASSWD,CLIENTID) VALUES ('dilbert','dogbert','dilbert-id')
                                   ]]></attribute>
                                   </mbean>
                                  
                                  </server>
                                  


                                  Can you give it another try?

                                  Thanks,

                                  Daniel

                                  • 14. Re: How to switch to MySQL?
                                    Haagen Hasle Novice

                                    I got this now:
                                    com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Table 'juddidb.jbm_postoffice' doesn't exist

                                    When I look inside the file you sent me, it seems to be a statement there to create this table, so I'm not sure what happens here..

                                    I have manually created the juddidb-database, and I guess I can create the jbm_postoffice-table as well. I'll try that and let you know what happens..

                                    Regards, Haagen

                                    1 2 3 4 Previous Next