11 Replies Latest reply on Aug 7, 2008 10:02 AM by Naveen Malik

    JBM SP3 CP02 with MySQL persistence not working

    Naveen Malik Newbie

      Recently switched from JBM SP3 CP01 to CP02. I am using MySQL for persistence and am unable to send a message to a queue successfully.

      My setup:

      Fedora 8
      MySQL community 5.1.22
      JBossAS 4.3.0.GA_CP01
      JBoss Messaging 1.4.0-1.SP3_CP02

      Setup:

      1. Install JBossAS
      2. Deploy a MessagingDS pointing to the MySQL server. The tables are already created in this database. DDL for table creation taken from the mysql persistence service from CP02.
      3. Delete deploy/jboss-messaging.sar/clustered-hsqldb-persistence-service.xml
      3. Copy the mysql-persistence-service.xml from the JBM release to deploy/jboss-messaging.sar
      4. Edit persistence service to use MessagingDS.
      5. Edit conf/login-config.xml to use MessagingDS for messaging.
      6. Deploy mysql-connector-java-5.0.7-bin.jar in server lib.

      java.lang.NullPointerException
       at java.util.concurrent.ConcurrentHashMap.put(ConcurrentHashMap.java:846)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager.cacheID(JDBCPersistenceManager.java:1632)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager$1AddReferenceRunner.doTransaction(JDBCPersistenceManager.java:1358)
       at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.execute(JDBCSupport.java:474)
       at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.executeWithRetry(JDBCSupport.java:512)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager.addReference(JDBCPersistenceManager.java:1396)
       at org.jboss.messaging.core.impl.ChannelSupport.handle(ChannelSupport.java:226)
       at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.routeInternal(MessagingPostOffice.java:2208)
       at org.jboss.messaging.core.impl.postoffice.MessagingPostOffice.route(MessagingPostOffice.java:494)
       at org.jboss.jms.server.endpoint.ServerConnectionEndpoint.sendMessage(ServerConnectionEndpoint.java:755)
       at org.jboss.jms.server.endpoint.ServerSessionEndpoint.send(ServerSessionEndpoint.java:391)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised.org$jboss$jms$server$endpoint$advised$SessionAdvised$send$aop(SessionAdvised.java:87)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
       at org.jboss.jms.server.container.SecurityAspect.handleSend(SecurityAspect.java:157)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.aop.advice.PerInstanceAdvice.invoke(PerInstanceAdvice.java:121)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised.send(SessionAdvised.java)
       at org.jboss.jms.wireformat.SessionSendRequest.serverInvoke(SessionSendRequest.java:95)
       at org.jboss.jms.server.remoting.JMSServerInvocationHandler.invoke(JMSServerInvocationHandler.java:143)
       at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:809)
       at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:572)
       at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:387)
       at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:166)
      2008-06-20 11:01:10,092 ERROR [org.jboss.messaging.util.ExceptionUtil] SessionEndpoint[i-8yetwohf-1-6k8swohf-c909sv-z1a6ba] send [j-0fftwohf-1-6k8swohf-c909sv-z1a6ba]
      javax.jms.JMSException: Failed to route Reference[19889751164256256]:RELIABLE to testQueue
       at org.jboss.jms.server.endpoint.ServerConnectionEndpoint.sendMessage(ServerConnectionEndpoint.java:757)
       at org.jboss.jms.server.endpoint.ServerSessionEndpoint.send(ServerSessionEndpoint.java:391)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised.org$jboss$jms$server$endpoint$advised$SessionAdvised$send$aop(SessionAdvised.java:87)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
       at org.jboss.jms.server.container.SecurityAspect.handleSend(SecurityAspect.java:157)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.aop.advice.PerInstanceAdvice.invoke(PerInstanceAdvice.java:121)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised$send_7280680627620114891.invokeNext(SessionAdvised$send_7280680627620114891.java)
       at org.jboss.jms.server.endpoint.advised.SessionAdvised.send(SessionAdvised.java)
       at org.jboss.jms.wireformat.SessionSendRequest.serverInvoke(SessionSendRequest.java:95)
       at org.jboss.jms.server.remoting.JMSServerInvocationHandler.invoke(JMSServerInvocationHandler.java:143)
       at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:809)
       at org.jboss.remoting.transport.socket.ServerThread.processInvocation(ServerThread.java:572)
       at org.jboss.remoting.transport.socket.ServerThread.dorun(ServerThread.java:387)
       at org.jboss.remoting.transport.socket.ServerThread.run(ServerThread.java:166)
      





        • 1. Re: JBM SP3 CP02 with MySQL persistence not working
          Andy Taylor Master

          does this happen on startup or whilst sending a message. If the former, could you try deleting the messages from JBM_MSG and JBM_MSG_REF tables.

          • 2. Re: JBM SP3 CP02 with MySQL persistence not working
            Tim Fox Master

            You need to drop your old tables too.

            • 3. Re: JBM SP3 CP02 with MySQL persistence not working
              Naveen Malik Newbie

              This happens when sending only. Startup occurs successfully. The tables were recreated prior to attempting this, so there is no data in the JBM tables. I had to create the tables by hand, though, because the create-on-startup didn't work. But, to create the tables I took the DDL from the mysql-presistence-service.xml file.

              • 4. Re: JBM SP3 CP02 with MySQL persistence not working
                Tim Fox Master

                Can you validate your installation by running the examples as per the user guide?

                • 5. Re: JBM SP3 CP02 with MySQL persistence not working
                  Naveen Malik Newbie

                  I can successfully send a message to a queue if I do not change the setup to use MySQL for message persistence. That is, do a base install of JBoss AS and it works out of the box with hsqldb and I can send a message to queue/testQueue. I did not use the process in the user guide to test this.

                  • 6. Re: JBM SP3 CP02 with MySQL persistence not working
                    Tim Fox Master

                    That's strange. The example should certainly work when you use the mysql config (does for me).

                    This strongly implies either a) wrong config is being used b) for some reason tables haven't been created properly.

                    I would check your config and make sure it is from the right place.

                    • 7. Re: JBM SP3 CP02 with MySQL persistence not working
                      Naveen Malik Newbie

                      I pulled the mysql-persistence-service.xml from svn. Here are the files I changed in jboss from the base install and the DDL used to create the tables:

                      messaging-ds.xml

                      <datasources>
                       <local-tx-datasource>
                       <jndi-name>MessagingDS</jndi-name>
                       <connection-url>jdbc:mysql://HOSTNAME:3306/messaging</connection-url>
                       <driver-class>com.mysql.jdbc.Driver</driver-class>
                       <user-name>USERNAME</user-name>
                       <password>PASSWORD</password>
                       <min-pool-size>5</min-pool-size>
                       <max-pool-size>20</max-pool-size>
                       <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
                       <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
                       <metadata>
                       <type-mapping>mySQL</type-mapping>
                       </metadata>
                       </local-tx-datasource>
                      </datasources>
                      


                      DDL for Messaging Tables
                      CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) ENGINE = INNODB;
                      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 INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE) ;
                      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 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 TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) ENGINE = INNODB;
                      CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER, CNTR INTEGER, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR)) ENGINE = INNODB;
                      INSERT INTO JBM_DUAL VALUES (1);
                      
                      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;
                      
                      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 TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) ENGINE = INNODB;
                      INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('guest', 'guest');
                      INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee');
                      INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample');
                      INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody');
                      INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub');
                      INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody');
                      


                      login-config.xml
                      
                      <?xml version='1.0'?>
                      <!DOCTYPE policy PUBLIC
                       "-//JBoss//DTD JBOSS Security Config 3.0//EN"
                       "http://www.jboss.org/j2ee/dtd/security_config.dtd">
                      
                      <!-- The XML based JAAS login configuration read by the
                      org.jboss.security.auth.login.XMLLoginConfig mbean. Add
                      an application-policy element for each security domain.
                      
                      The outline of the application-policy is:
                      <application-policy name="security-domain-name">
                       <authentication>
                       <login-module code="login.module1.class.name" flag="control_flag">
                       <module-option name = "option1-name">option1-value</module-option>
                       <module-option name = "option2-name">option2-value</module-option>
                       ...
                       </login-module>
                      
                       <login-module code="login.module2.class.name" flag="control_flag">
                       ...
                       </login-module>
                       ...
                       </authentication>
                      </application-policy>
                      
                      $Revision: 65777 $
                      -->
                      
                      <policy>
                       <!-- Used by clients within the application server VM such as
                       mbeans and servlets that access EJBs.
                       -->
                       <application-policy name = "client-login">
                       <authentication>
                       <login-module code = "org.jboss.security.ClientLoginModule"
                       flag = "required">
                       <!-- Any existing security context will be restored on logout -->
                       <module-option name="restore-login-identity">true</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!-- Security domain for JBossMQ -->
                       <application-policy name = "jbossmq">
                       <authentication>
                       <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
                       flag = "required">
                       <module-option name = "unauthenticatedIdentity">guest</module-option>
                       <module-option name = "dsJndiName">java:/DefaultDS</module-option>
                       <module-option name = "principalsQuery">SELECT PASSWD FROM JMS_USERS WHERE USERID=?</module-option>
                       <module-option name = "rolesQuery">SELECT ROLEID, 'Roles' FROM JMS_ROLES WHERE USERID=?</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!-- Security domain for JBossMQ when using file-state-service.xml
                       <application-policy name = "jbossmq">
                       <authentication>
                       <login-module code = "org.jboss.mq.sm.file.DynamicLoginModule"
                       flag = "required">
                       <module-option name = "unauthenticatedIdentity">guest</module-option>
                       <module-option name = "sm.objectname">jboss.mq:service=StateManager</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                       -->
                      
                       <!-- Security domains for testing new jca framework -->
                       <application-policy name = "HsqlDbRealm">
                       <authentication>
                       <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
                       flag = "required">
                       <module-option name = "principal">sa</module-option>
                       <module-option name = "userName">sa</module-option>
                       <module-option name = "password"></module-option>
                       <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <application-policy name = "JmsXARealm">
                       <authentication>
                       <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
                       flag = "required">
                       <module-option name = "principal">guest</module-option>
                       <module-option name = "userName">guest</module-option>
                       <module-option name = "password">guest</module-option>
                       <module-option name = "managedConnectionFactoryName">jboss.jca:service=TxCM,name=JmsXA</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!-- A template configuration for the jmx-console web application. This
                       defaults to the UsersRolesLoginModule the same as other and should be
                       changed to a stronger authentication mechanism as required.
                       -->
                       <application-policy name = "jmx-console">
                       <authentication>
                       <login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule"
                       flag = "required">
                       <module-option name="usersProperties">props/jmx-console-users.properties</module-option>
                       <module-option name="rolesProperties">props/jmx-console-roles.properties</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!-- A template configuration for the web-console web application. This
                       defaults to the UsersRolesLoginModule the same as other and should be
                       changed to a stronger authentication mechanism as required.
                       -->
                       <application-policy name = "web-console">
                       <authentication>
                       <login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule"
                       flag = "required">
                       <module-option name="usersProperties">web-console-users.properties</module-option>
                       <module-option name="rolesProperties">web-console-roles.properties</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!--
                       A template configuration for the JBossWS security domain.
                       This defaults to the UsersRolesLoginModule the same as other and should be
                       changed to a stronger authentication mechanism as required.
                       -->
                       <application-policy name="JBossWS">
                       <authentication>
                       <login-module code="org.jboss.security.auth.spi.UsersRolesLoginModule"
                       flag="required">
                       <module-option name="usersProperties">props/jbossws-users.properties</module-option>
                       <module-option name="rolesProperties">props/jbossws-roles.properties</module-option>
                       <module-option name="unauthenticatedIdentity">anonymous</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <application-policy name="messaging">
                       <authentication>
                       <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule"
                       flag = "required">
                       <module-option name = "unauthenticatedIdentity">guest</module-option>
                       <module-option name = "dsJndiName">java:/MessagingDS</module-option>
                       <module-option name = "principalsQuery">SELECT PASSWD FROM JBM_USER WHERE USER_ID=?</module-option>
                       <module-option name = "rolesQuery">SELECT ROLE_ID, 'Roles' FROM JBM_ROLE WHERE USER_ID=?</module-option>
                       </login-module>
                       </authentication>
                       </application-policy>
                      
                       <!-- The default login configuration used by any security domain that
                       does not have a application-policy entry with a matching name
                       -->
                       <application-policy name = "other">
                       <!-- A simple server login module, which can be used when the number
                       of users is relatively small. It uses two properties files:
                       users.properties, which holds users (key) and their password (value).
                       roles.properties, which holds users (key) and a comma-separated list of
                       their roles (value).
                       The unauthenticatedIdentity property defines the name of the principal
                       that will be used when a null username and password are presented as is
                       the case for an unuathenticated web client or MDB. If you want to
                       allow such users to be authenticated add the property, e.g.,
                       unauthenticatedIdentity="nobody"
                       -->
                       <authentication>
                       <login-module code = "org.jboss.security.auth.spi.UsersRolesLoginModule"
                       flag = "required" />
                       </authentication>
                       </application-policy>
                      
                      </policy>
                      

                      mysql-persistence-service.xml
                      <?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=MessagingDS</depends>
                      
                       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                      
                       <!-- The datasource to use for the persistence manager -->
                      
                       <attribute name="DataSource">java:/MessagingDS</attribute>
                      
                       <!-- If true will attempt to create tables and indexes on every start-up -->
                      
                       <attribute name="CreateTablesOnStartup">true</attribute>
                      
                       <!-- If true then we will automatically detect and reject duplicate messages sent during failover -->
                      
                       <attribute name="DetectDuplicates">true</attribute>
                      
                       <!-- The size of the id cache to use when detecting duplicate messages -->
                      
                       <attribute name="IDCacheSize">500</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_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
                       CREATE_ID_CACHE=CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER, CNTR INTEGER, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR)) 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 = ?
                       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=?
                       UPDATE_ID_IN_CACHE=UPDATE JBM_ID_CACHE SET JBM_ID = ? WHERE NODE_ID = ? AND CNTR = ?
                       INSERT_ID_IN_CACHE=INSERT INTO JBM_ID_CACHE (NODE_ID, CNTR, JBM_ID) VALUES (?, ?, ?)
                       LOAD_ID_CACHE=SELECT CNTR, JBM_ID FROM JBM_ID_CACHE 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=MessagingDS</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:/MessagingDS</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">30000</attribute>
                      
                       <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
                      
                       <attribute name="CastTimeout">30000</attribute>
                      
                       <!-- Set this to true if you want failover of connections to occur when a node is shut down -->
                      
                       <attribute name="FailoverOnNodeLeave">false</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,jboss.partition.udpGroup: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,jboss.partition.udpGroup: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:8}"
                       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=MessagingDS</depends>
                       <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
                       <attribute name="DataSource">java:/MessagingDS</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>
                      


                      • 8. Re: JBM SP3 CP02 with MySQL persistence not working
                        Tim Fox Master

                        Why don't you just let JBM create the tables?

                        If you're manually creating the tables then that's scope for human error. If you expect me to manually go through what you posted and diff it to the correct version at 7pm on a Friday night, you've got another thing coming ;)

                        Also you didn't say where in SVN you got the file from.

                        • 9. Re: JBM SP3 CP02 with MySQL persistence not working
                          Naveen Malik Newbie

                          svn info

                          URL: http://anonsvn.jboss.org/repos/messaging/tags/JBossMessaging_1_4_0_SP3_CP02
                          Repository Root: http://anonsvn.jboss.org/repos/messaging
                          Repository UUID: b6fe21fe-e719-0410-baf4-a14e395396c4
                          Revision: 4519
                          Node Kind: directory
                          Schedule: normal
                          Last Changed Author: timfox
                          Last Changed Rev: 4012
                          Last Changed Date: 2008-04-04 12:10:19 -0400 (Fri, 04 Apr 2008)


                          I was creating them by hand because JBM was initially failing to create the tables. I just tried create-on-startup and it worked, JBM created the tables correctly. So ignore the DDL in my previous comment. Testing again with the newly created tables results in the same exception that I originally posted.

                          • 10. Re: JBM SP3 CP02 with MySQL persistence not working
                            Clebert Suconic Master

                            I just did a test from scratch with EAP and I couldn't get any bugs.

                            You're messing up with something basic here. What you're reporting is quite a common use case. If this was a bug we would have been informed right after the CP01 release with way many more requests.

                            • 11. Re: JBM SP3 CP02 with MySQL persistence not working
                              Naveen Malik Newbie

                              Found that the jboss-messaging-client.jar from EAP 4.3.0.GA is not compatible with EAP 4.3.0.GA_CP01. The fix is to upgrade the jboss-messaging-client.jar on the client.