5 Replies Latest reply on Jan 30, 2004 4:33 AM by geirwilly

    Mapping JBossMQ persistence manager to mysql

    geirwilly
    geirwilly Jan 16, 2004 5:25 AM

      You have an error in your SQL syntax near 'JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_T

      I am running jboss 3.2.3, and mysql 3.23.58. using mm.mysql.jar as jdbc-driver.
      I want to map the JBossMQ persistence manager to mysql, not using hypersonic db.
      I have added a mysql-ds.xml with a datasource named MySqlDS, and copied the mysql-jdbc2-service.xml from docs/examples/jms to deploy/jms.

      I was running hsqldb-ds.xml and using DefaultDS as message cache, and found a reply on jboss forums saying:
      As from 3.2.1, JBossMQ persistence manager is mapper to the HSQL DB.

      I would expect it to be possible to change this mapping.


      do I need to add a datasource=MySqlDS with datasource-mapping=mySQL in standardjbosscmp-jdbc.xml.

      what about standardjaws.xml (datasource-mapping?)
      login-config.xml?
      What need to be changed

      mysql-ds.xml:
      <local-tx-datasource>
      <jndi-name>MySqlDS</jndi-name>
      <connection-url>
      jdbc:mysql://localhost:3306/messagecache
      </connection-url>
      <driver-class>org.gjt.mm.mysql.Driver</driver-class>
      <user-name>username</user-name>
      password
      </local-tx-datasource>


      standardjbosscmp-jdbc.xml

      java:/DefaultDS
      <datasource-mapping>Hypersonic SQL</datasource-mapping>
      <!--<datasource-mapping>mySQL</datasource-mapping>-->


      The error on startup:

      anager 'jboss.jca:service=LocalTxCM,name=DefaultDS to JNDI name 'java:/DefaultDS'
      2004-01-16 11:07:10,151 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=DefaultDS
      2004-01-16 11:07:10,171 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.jdbc/JPipeDS] Bound connection factory for resource adapter for Connecti
      onManager 'jboss.jca:service=LocalTxCM,name=jdbc/JPipeDS to JNDI name 'java:/jdbc/JPipeDS'
      2004-01-16 11:07:10,172 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=jdbc/JPipeDS
      2004-01-16 11:07:10,174 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.MySqlDS] Bound connection factory for resource adapter for ConnectionMan
      ager 'jboss.jca:service=LocalTxCM,name=MySqlDS to JNDI name 'java:/MySqlDS'
      2004-01-16 11:07:10,174 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=MySqlDS
      2004-01-16 11:07:10,571 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: Syntax error or
      access violation: You have an error in your SQL syntax near 'JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_T' at line 1)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:336)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1356)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
      at $Proxy14.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:394)
      at org.jboss.system.ServiceController.start(ServiceController.java:411)
      at org.jboss.system.ServiceController.start(ServiceController.java:411)
      at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)

      • 2504 Views
      • Tags:
        • 1. Re: Mapping JBossMQ persistence manager to mysql
          adrian.brock Jan 16, 2004 6:01 AM (in response to geirwilly)

          You can find an example for mysql in docs/examples/jms

          Regards,
          Adrian

            • Actions
          • 2. Re: Mapping JBossMQ persistence manager to mysql
            geirwilly
            geirwilly Jan 16, 2004 6:40 AM (in response to geirwilly)

            Yes I did use the docs/examples/jms to find mysql-jdbc2-service.xml.
            Seems like it is the sql

            DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?

            in the mysql-jdbc2-service.xml that is failing.


            copy of the mysql-jdbc2-service.xml


            <depends optional-attribute-name="ConnectionManager">jboss.jca:service=LocalTxCM,name=MySqlDS

            BLOB_TYPE=BYTES_BLOB
            INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
            INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
            SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
            SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES
            SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
            SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
            MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
            UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
            UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
            UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
            UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
            UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
            UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
            DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?
            DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
            DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
            DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
            CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES (MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(150) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION))
            CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)
            CREATE_TABLES_ON_STARTUP = TRUE

              • Actions
            • 3. Re: Mapping JBossMQ persistence manager to mysql
              adrian.brock Jan 16, 2004 7:01 AM (in response to geirwilly)

              Ok,

              I thought the guy who contributed this used the strange delete through a join because
              mysql3 doesn't support subselects.
              It should work on mysql4.

              Does your version of mysql support transactions?

              Regards,
              Adrian

                • Actions
              • 4. Re: Mapping JBossMQ persistence manager to mysql
                geirwilly
                geirwilly Jan 20, 2004 2:49 PM (in response to geirwilly)

                Yes you are right, it worked fine on mysql version 4.0.16.

                Now the question is, how to get it to work on mysql 3.23. Do I need to get above 3.23.49 (InnoDB transactional storage engine fully supported). How do I configure it to work, or is upgrading mysql the only way out?

                  • Actions
                • 5. Re: Mapping JBossMQ persistence manager to mysql
                  geirwilly
                  geirwilly Jan 30, 2004 4:33 AM (in response to geirwilly)

                  The problem is actually not transactional support or not, but support for multitable delete statements.

                  From http://forums.devshed.com/archive/4/2002/11/4/47665

                  The multitable delete you have there should work totally fine in MySQL 4.0, however 3.23 doesn't have multitable delete statements. In 4.1 you could either use subselects or the left join (the left join being more efficient most likely). In 3.23, which I assume you are using since you got a syntax error, you will have to do it client side. You could easily make MySQL return the actual select statements you need to run and just pipe them back to MySQL or something similar.


                  Is there someone who have done this "client side"?

                    • Actions