4 Replies Latest reply on Nov 29, 2004 8:06 PM by Alec Lynch

    mySQL Persistence Manager / JMS Problems

    Alec Lynch Newbie

      Hi

      I've followed instructions to make mysql the DefaultDS in Jboss but I'm having problems with jms. I created the mysql-ds.xml file and copied the mysql-jdbc2-service.xml file from examples to the default/deploy/jms directory.

      ***PROBLEM
      The persistence manager does not start. When I start Jboss all the tables are created. JMS_USERS, JMS_MESSAGES, JMS_TRANSACTIONS etc. But it falters on some other SQL statement (apparently because of syntax, see the log below).

      ***OTHER CHANGES
      In mysql-jdbc2-service.xml I changed
      "CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER, PRIMARY KEY (TXID))"

      to

      "CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)"

      I changed this because I was getting a SQL syntax error via JBOSS that TXID needed to be NOT NULL as it was a primary key.

      I also changed "INTERVAL" to "INTERVALS" and "other" to "text" in ejb-deployer.xml so that the timer table could be created.

      I'm not sure if these changes that I've made are causing my current problem or not (they fixed the problems I was facing at the time), but I thought I should let you know.

      ***LOG
      ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed jboss.mq:service=PersistenceManager
      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, message from server: "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")

      ***mysql-jdbc2-service.xml

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

      ***mysql-ds.xml

      <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>jboss</user-name>
      password

      <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

      <type-mapping>mySQL</type-mapping>

      </local-tx-datasource>


      ***standardjaws.xml

      java:/DefaultDS
      <type-mapping>mySQL</type-mapping>

      ***standardjbosscmp-jdbc.xml
      <jbosscmp-jdbc>

      java:/DefaultDS
      <datasource-mapping>mySQL</datasource-mapping>

      ***Specs
      J Connector = mysql-connector-java-3.0.11-stable
      MySQL = 3.2.1
      JBOSS = 4.0.0
      OS = Windows XP

      ***Help?
      If you have any ideas about this problem please let me know.
      Do I need j connector 3.0.9?
      Do I need to upgrade to a later release (RC??) of JBOSS?
      Could this be anything to do with privileges of the jboss user in mysql? I used grant all but I'm not positive that it worked?

        • 1. Re: mySQL Persistence Manager / JMS Problems
          Darran Lofthouse Master

          Did you remove the file 'hsqldb-jdbc2-service.xml' from the jms folder?

          • 2. Re: mySQL Persistence Manager / JMS Problems
            Alec Lynch Newbie

            Thanks for your reply, this problem is killing me.

            "darranl" wrote:
            Did you remove the file 'hsqldb-jdbc2-service.xml' from the jms folder?


            Yes, I did.

            Well I haven't had much progress, but this is what I've come across.

            In the mysql-jdbc2-service.xml file under the PersistenceManager mbean, there is a line:
            "DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?"

            This seems to be the line of code that is causing the errors in the log. I think it is a syntax problem. I dumped this truncated version of this sql into a normal sql query (outside of Jboss in MySQLManager):
            "DELETE JMS_MESSAGES FROM JMS_MESSAGES"
            This sql statement didn't work. It threw an syntax exception (with not much explanation). So this seems to be the problem.

            Maybe I have stuffed up in one of the mapping files and it's not converting the sql to mysql-specific sql. Does that make sense?

            Or maybe my version of mysql is too old. I'm contemplating getting 4.1 ... But I'm not confident that will fix the problem because I don't think (well I'm guessing) that the syntax of DELETE statements hasn't changed much in the past 100 years if you catch my drift. I read somewhere (I forget where) that mysql 4.1 is the first mysql release to handle nested sql or something along those lines, and I *think* I remember reading that this was somehow related to my current problem.

            Please reply if you have any ideas!







            • 3. Re: mySQL Persistence Manager / JMS Problems
              Alec Lynch Newbie

               

              From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables


              How about that, DELETE syntax has changed.

              Seems that my version of mysql does not support delete statements which reference more than 1 table.

              I hope that mysql 4.1.7 fixes my problems.



              • 4. Re: mySQL Persistence Manager / JMS Problems
                Alec Lynch Newbie

                It did fix my problem.

                If you're using jboss 4.0 or later with mysql, you should probably use mysql 4.0 or later.