JBossDeveloper

I'm pretty sure you are running into :

http://jira.jboss.com/jira/browse/JBESB-1310

I'm guessing you are doing this within the ESB server?

If you try it on trunk, it should be fine.

I'm pretty sure you are running into :

http://jira.jboss.com/jira/browse/JBESB-1310

I'm guessing you are doing this within the ESB server?

If you try it on trunk, it should be fine.

I've logged http://jira.jboss.com/jira/browse/JBESB-1348 to represent this. I'm pretty sure that this is fixed on the branch, but I'll merge it over to the trunk for you next week.

After 2 hours, I changed all the files to get JBossESB working with MySQL. These files should be documented to avoid searching "HSQL", an replacing with MySQL:

./server/default/deploy/jbpm.esb/jbpm-service.xml
./server/default/deploy/jbpm.esb/META-INF/jboss-esb.xml
./server/default/deploy/jbpm.esb/hibernate.cfg.xml
./server/default/deploy/jbpm.esb/jbpm-ds.xml

./server/default/deploy/jbossesb.sar/jbossesb-properties.xml
./server/default/deploy/jbossesb.sar/esb.juddi.xml
./server/default/deploy/jbossesb.sar/META-INF/jboss-service.xml
./server/default/deploy/jbossesb.sar/juddi-ds.xml

./server/default/deploy/jbossesb.esb/jbossesb-service.xml
./server/default/deploy/jbossesb.esb/message-store-ds.xml
./server/default/deploy/jbossesb.esb/META-INF/jboss-esb.xml

Regards,
Eric

Hi,
i want to swich all to mySql v 5, but after read all the posts i have this error:

--- MBeans waiting for other MBeans ---
ObjectName: jboss.messaging:service=PersistenceManager
 State: FAILED
 Reason: java.lang.IllegalArgumentException: No such SQL statement: CREATE_IDX_
MESSAGE_TIMESTAMP
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS
 jboss:service=TransactionManager
 Depends On Me:
 jboss.messaging:service=ServerPeer

ObjectName: jboss.messaging:service=JMSUserManager
 State: FAILED
 Reason: java.lang.IllegalArgumentException: No such SQL statement: PRIMARY
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS
 jboss:service=TransactionManager
 Depends On Me:
 jboss.messaging:service=ServerPeer

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.messaging:service=PersistenceManager
 State: FAILED
 Reason: java.lang.IllegalArgumentException: No such SQL statement: CREATE_IDX_
MESSAGE_TIMESTAMP
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS
 jboss:service=TransactionManager
 Depends On Me:
 jboss.messaging:service=ServerPeer

ObjectName: jboss.messaging:service=JMSUserManager
 State: FAILED
 Reason: java.lang.IllegalArgumentException: No such SQL statement: PRIMARY
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS
 jboss:service=TransactionManager
 Depends On Me:
 jboss.messaging:service=ServerPeer

Hi,

is there any more information to be found in the server.log?

regards,

/Daniel

Hi Daniel,
i am getting the same error as "Sm0k3rz" while restarting the server.
I am trying to switch from HSQLDB to MySQL, so i have replaced "...\jbossesb-server-4.4.GA\server\default\deploy\hsqldb-ds.xml" with "...\jbossesb-server-4.4.GA\server\default\deploy\mysql-ds.xml" and put the below code in "...\jbossesb-server-4.4.GA\server\default\deploy\jboss-messaging.sar\hsqldb-persistence-service.xml" file

<?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, TRANSACT
ION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BI
GINT, 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 (SCH
ED_DELIVERY)
 CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIME
STAMP 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 VARBI
NARY(254), FORMAT_ID INTEGER, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID)) ENGINE = INN
ODB
 CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) EN
GINE = 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, P
AGE_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 CHAN
NEL_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_I
D=? AND STATE='-'
 LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHER
E 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' AN
D CHANNEL_ID = ? ORDER BY ORD
 UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND C
HANNEL_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, TYP
E 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, PRIO
RITY, TYPE, INS_TIME) SELECT ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID F
ROM 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 NO
T 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_TX
ID) 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 CH
AR(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, CHA
NNEL_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_POSTOFFIC
E 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','dilber
t-id')
 ]]></attribute>
 </mbean>

</server>

<?xml version="1.0" encoding="UTF-8"?>

<!-- $Id: mysql-ds.xml 63175 2007-05-21 16:26:06Z rrajesh $ -->
<!-- Datasource config for MySQL using 3.0.9 available from:
http://www.mysql.com/downloads/api-jdbc-stable.html
-->

<datasources>
 <local-tx-datasource>
 <jndi-name>DefaultDS</jndi-name>
 <connection-url>jdbc:mysql://127.0.0.1:3306/localDB</connection-url>
 <driver-class>com.mysql.jdbc.Driver</driver-class>
 <user-name>test</user-name>
 <password>test</password>
 <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
 <!-- should only be used on drivers after 3.22.1 with "ping" support
 <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
 -->
 <!-- sql to call when connection is created
 <new-connection-sql>some arbitrary sql</new-connection-sql>
 -->
 <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers
 <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
 -->

 <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
 <metadata>
 <type-mapping>mySQL</type-mapping>
 </metadata>
 </local-tx-datasource>
</datasources>

Hi,

can you just confirm that you have followed the step described here:
http://www.jboss.org/community/docs/DOC-10368

If you have followed them then I'll try this with mysql to reproduce the issue you both are seeing.

Thanks,

/Daniel

Thanks Daniel, it worked.
The problem was "mysql-persistence-service.xml" this file was not compatible with my current JBM version.
I downloaded the "jboss-messaging-1.4.0.SP3-src" from jboss site and copied this file from "...\jboss-messaging-1.4.0.SP3-srcsrc\etc\server\default\deploy" to "...\jbossesb-server-4.4.GA\server\default\deploy\jboss-messaging.sar", and deleted the existing file "hsqldb-persistence-service.xml".

But i think there is an issue, system countinuously tries to fire the query of wrong format until i restart the server. I think it should try for any predefined max count.

BTW the document you asked to refer is very useful.

Thanks again for your help.

Thanks to all,
i have substitute the persistence manager from: http://anonsvn.jboss.org/repos/messaging/branches/Branch_JBossMessaging_1_4_0_SP3_CP/src/etc/server/default/deploy/mysql-persistence-service.xml.

But now i have another error :

12:27:46,453 INFO [ServiceConfigurator] Problem configuring service jboss.messa
ging:service=PersistenceManager
org.jboss.deployment.DeploymentException: No Attribute found with name: DetectDu
plicates
 at org.jboss.system.ServiceConfigurator.configure(ServiceConfigurator.ja
va:318)
 at org.jboss.system.ServiceConfigurator.internalInstall(ServiceConfigura
tor.java:462)
 at org.jboss.system.ServiceConfigurator.install(ServiceConfigurator.java
:171)
 at org.jboss.system.ServiceController.install(ServiceController.java:226
)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy4.install(Unknown Source)
 at org.jboss.deployment.SARDeployer.create(SARDeployer.java:249)
 at org.jboss.deployment.MainDeployer.create(MainDeployer.java:969)
 at org.jboss.deployment.MainDeployer.create(MainDeployer.java:959)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:818)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:133)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:142)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy9.deploy(Unknown Source)
 at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymen
tScanner.java:421)
 at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentS
canner.java:634)
 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.
doScan(AbstractDeploymentScanner.java:263)
 at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(A
bstractDeploymentScanner.java:336)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
upport.java:289)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
eanSupport.java:245)
 at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
ler.java:978)
 at $Proxy0.start(Unknown Source)
 at org.jboss.system.ServiceController.start(ServiceController.java:417)
 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy4.start(Unknown Source)
 at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:133)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:142)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy5.deploy(Unknown Source)
 at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
 at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
 at org.jboss.Main.boot(Main.java:200)
 at org.jboss.Main$1.run(Main.java:508)
 at java.lang.Thread.run(Thread.java:595)
12:27:46,484 INFO [ServiceConfigurator] Problem configuring service jboss.messa
ging:service=PostOffice
org.jboss.deployment.DeploymentException: No Attribute found with name: Failover
OnNodeLeave
 at org.jboss.system.ServiceConfigurator.configure(ServiceConfigurator.ja
va:318)
 at org.jboss.system.ServiceConfigurator.internalInstall(ServiceConfigura
tor.java:462)
 at org.jboss.system.ServiceConfigurator.install(ServiceConfigurator.java
:171)
 at org.jboss.system.ServiceController.install(ServiceController.java:226
)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy4.install(Unknown Source)
 at org.jboss.deployment.SARDeployer.create(SARDeployer.java:249)
 at org.jboss.deployment.MainDeployer.create(MainDeployer.java:969)
 at org.jboss.deployment.MainDeployer.create(MainDeployer.java:959)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:818)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:133)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:142)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy9.deploy(Unknown Source)
 at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymen
tScanner.java:421)
 at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentS
canner.java:634)
 at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.
doScan(AbstractDeploymentScanner.java:263)
 at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(A
bstractDeploymentScanner.java:336)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
upport.java:289)
 at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
eanSupport.java:245)
 at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
ler.java:978)
 at $Proxy0.start(Unknown Source)
 at org.jboss.system.ServiceController.start(ServiceController.java:417)
 at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy4.start(Unknown Source)
 at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
 at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
 at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
 at java.lang.reflect.Method.invoke(Method.java:585)
 at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:155)
 at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
 at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:133)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:142)
 at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
 at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:264)
 at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
 at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
 at $Proxy5.deploy(Unknown Source)
 at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
 at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
 at org.jboss.Main.boot(Main.java:200)
 at org.jboss.Main$1.run(Main.java:508)
 at java.lang.Thread.run(Thread.java:595)

Please replace the contents of "mysql-persistence-service.xml" with below code, and check if it works. Since its working for me.

<?xml version="1.0" encoding="UTF-8"?>

<!--
 MySql persistence deployment descriptor.

 Tested with MySQL 5.0.27

 $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 (MESSAGE_ID BIGINT, CHANNEL_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID)) ENGINE = INNODB
 CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE)
 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, HEADERS MEDIUMBLOB, PAYLOAD LONGBLOB, PRIMARY KEY (MESSAGE_ID)) ENGINE = INNODB
 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 MESSAGE_ID = ? AND CHANNEL_ID = ?
 UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
 MOVE_REFERENCE=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE MESSAGE_ID = ? AND 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=?
 INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
 MESSAGE_ID_COLUMN=MESSAGE_ID
 DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID 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">300000</attribute>

 <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->

 <attribute name="CastTimeout">300000</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"
 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) VALUES ('guest', 'guest')
POPULATE.TABLES.2 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee')
POPULATE.TABLES.3 = INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
POPULATE.TABLES.4 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody')
POPULATE.TABLES.5 = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub')
POPULATE.TABLES.6 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest')
POPULATE.TABLES.7 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest')
POPULATE.TABLES.8 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest')
POPULATE.TABLES.9 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john')
POPULATE.TABLES.10 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john')
POPULATE.TABLES.11 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub')
POPULATE.TABLES.12 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john')
POPULATE.TABLES.13 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub')
POPULATE.TABLES.14 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody')
 ]]></attribute>
 </mbean>

</server>