6 Replies Latest reply on Jul 22, 2003 3:29 AM by masj

    Replacing Hypersonic with MySQL as DefaultDS

    masj Newbie

      Hi!

      I am trying to replace Hypersonic with MySQL 4.01 as DefaultDS in JBoss 3.2.1.
      I have tried to undeploy hsqldb-ds.xml and set MySQL:s JNDI-name to DefaultDS in mysql-ds.xml. I have also changed the tag <type-mapping> to mySQL in standardjaws.xml and standardjbosscmp-jdbc.xml.
      When I then start JBoss it runs well until it suddenly casts an exception:

      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. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1")

      What could be the problem?

        • 2. Re: Replacing Hypersonic with MySQL as DefaultDS
          Juha Lindfors Master

          JBossMQ is the problem. It's default persistence manager is now JDBC persistence which by default attempts to connect to and use the datasource bound to DefaultDS

          Check your deploy/jms/jbossmq-service.xml file, and either enable the file based MQ message persistence or see if you can reconfigure the SQL to work with MySQL (I couldn't find the exact match for the above mentioned error message).

          Or another alternative is to keep HSQL deployed under a different name and reconfigure MQ JDBC persistence to use that instead.

          • 3. Re: Replacing Hypersonic with MySQL as DefaultDS
            carstens Newbie

            Working on the same problem, and found out the following:

            In the file deployed/jms/jbossmq-service.xml there are SQL-statements.

            To create the tables:
            CREATE_MESSAGE_TABLE: the type OBJECT has to be replaced by BLOB (...., MESSAGEBLOB OBJECT, ... -> ..., MESSAGEBLOB BLOB, ...)

            There is another line:
            BLOB_TYPE=BLOB_OBJECT, don't know, if here has to got BLOB_TYPE=BLOB as well.

            The described error arises from:
            DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXID IN (SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP=?

            mySQL is not able to handle subselects (... IN(SELECT ...)


            Carsten

            • 5. Re: Replacing Hypersonic with MySQL as DefaultDS
              Roger Ackroyd Newbie

              Can confirm that MySql 4.1.0 alpha supports nested queriries so that (probably) solves one issue if you want to trust to an alpha release.
              Am now looking for solution to BLOB issue as failures now occur when running cmp2 (and many other) tests from testsuite similarly, as follows

              11:52:24,696 ERROR [EntityContainer] Starting failed
              org.jboss.deployment.DeploymentException: Error while creating table CMRBugEJB; - nested throwable:
              (java.sql.SQLException: General error, message from server: "BLOB column 'id' used in key specifica
              tion without a key length")

              Am working to find and hopefully correct, but perhaps someone out there with a bit more exp. on Jboss/MySql can finger it straight away.
              Have tried changing to different blob mappings in standardjbosscmp-jdbc but to no avail.

              Please help, this is holding me up something rotten (unless I give up and just use Hypersonic). I promise I will post a set of instructions for resolving the issue if it can be resolved. There is a tendency for developers on here to become so elated at fixing a problem and getting on, that they entirely forget to share what they finally did to fix with everybody else.

              • 6. Re: Replacing Hypersonic with MySQL as DefaultDS
                masj Newbie

                In the project I am working on, I am forced to use the latest stable version of MySQL, which is 4.0.13. So I had to give up on using MySQL and JMS. Instead I used the tip Juha Lindfors gave: deploying HyperSonic under a different name and reconfiguring JMS. It works for now, until MySQL is released in a new stable version.

                Thanks for the tips!

                -Mattias, Sweden