How to switch to MySQL?| JBoss.org Content Archive (Read Only)

1. Re: How to switch to MySQL?

beve Nov 2, 2007 6:50 AM (in response to haagenhasle)

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?

beve Nov 2, 2007 6:56 AM (in response to haagenhasle)

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?

kconner Nov 2, 2007 6:57 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 8:15 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 9:11 AM (in response to haagenhasle)

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?

beve Nov 2, 2007 9:45 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 9:55 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 10:04 AM (in response to haagenhasle)

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

Regards, Haagen

9. Re: How to switch to MySQL?

beve Nov 2, 2007 10:09 AM (in response to haagenhasle)

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?

beve Nov 2, 2007 10:12 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 10:17 AM (in response to haagenhasle)

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

Regards, Haagen

12. Re: How to switch to MySQL?

haagenhasle Nov 2, 2007 10:19 AM (in response to haagenhasle)

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?

beve Nov 2, 2007 10:19 AM (in response to haagenhasle)

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?

haagenhasle Nov 2, 2007 10:25 AM (in response to haagenhasle)

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