7 Replies Latest reply on Aug 23, 2003 9:51 AM by Adrian Brock

    mySQL adequate for jbossmq?

    tom.purvis Newbie

      I'm new to JBoss, and I'm embarking on a small project
      to build an accounting application using EJBs, JBoss,
      and an open source rdbms.

      This week I've been setting up a development
      environment on a win2K machine. I had mysql 3.23.53
      already on the machine, so I installed JBoss 3.2.1
      (jboss-3.2.1 tomcat-4.1.24) and started about the
      process of defining mySQL as the datasource. After
      some stumbling and reading, I've gotten hypersonic
      replaced and mysql working.

      Two problems needed to be resolved to get JBoss
      to init without throwing exceptions. Both were syntax
      tweaks in SqlProperties in jbossmq-service.xml.
      One was CREATE_MESSAGE_TABLE, and I'm not
      worried about it, the other was in
      DELETE_MARKED_MESSAGES_WITH_TX, and I'm a
      little concerned about it.

      mySQL barfed on:

      DELETE FROM JMS_MESSAGES WHERE
      TXID IN (SELECT TXID FROM JMS_TRANSACTIONS)
      AND TXOP=?

      I did some reading and found that pre-4.x versions of
      mySQL did not support subselects, so I downloaded
      and installed 4.0.13. Still barfed on that statement.

      I spent quite a bit of time trying resolve the problems
      with the WHERE clause in this statement by running it
      as a select from dbVisualizer. I had no luck getting the
      where clause to work, even after refererring to the
      mysql docs about this new "feature". Hmmf. Maybe
      it would work with mySQL 4.1.x, but that seems like
      a really new version. If I have to go with a nosebleed
      version just to use mysql, I'm not sure I'm interested.

      Anyway, I did get it to work by re-writing the above
      delete statement this way:

      DELETE FROM JMS_MESSAGES WHERE TXOP=?

      Seems fairly benign, since the other version would
      only exclude JMS_MESSAGE records that are orphaned
      from JMS_TRANSACTIONS, but I really know nothing
      about how that data is used.

      Am I foolish to try to use mySQL as the db behind
      my J2EE applications? Is there a better way for me
      to help jbossmq make friends with mysql? Any other
      gotchas I should be watching for?

      Thanks in advance for help.

        • 1. Re: mySQL adequate for jbossmq?
          Adrian Brock Master

          The jbossmq persistence manager only uses
          local transactions, so mysql will be fine.

          I did check that mysql now supports subselects
          before including that statement in the configuration.

          If you want to use an older version, you can
          always rewrite the persistence manager to
          use a rowset to select all messages then
          check one-by-one whether there is a transaction.
          This is what previous versions of jdbc2 did,
          but it is horribly slow.

          Technically, the semantics of this processing is
          incorrect anyway. It should not just blindly rollback
          all transactions at recovery.
          It will be fixed when I find time.

          Regards,
          Adrian

          • 2. Re: mySQL adequate for jbossmq?
            Chris Lamprecht Newbie

            According to the MySQL web site, MySQL is adding subselects in 4.1, which is currently in alpha:

            http://www.mysql.com/doc/en/Nutshell_4.1_features.html

            4.0 does not have subselects apparently.

            • 3. Re: mySQL adequate for jbossmq?
              tom.purvis Newbie

              camel wrote:
              >According to the MySQL web site, MySQL is adding
              >subselects in 4.1, which is currently in alpha:
              >
              >http://www.mysql.com/doc/en/Nutshell_4.1_features.html
              >
              >4.0 does not have subselects apparently.

              Pretty clear now that I'm looking at it. I think I must
              have read a usenet post or something that said
              subselects were in MySQL 4.x and not 4.1 and later.

              I think I'll just proceed. I'm not going to work with
              alpha 4.1. But swapping the DS isn't that big of a
              deal from what I learned yesterday, so if I run into
              problems I can probably just change rdbms. Perhaps I
              could leave hsqldb in place as the DefaultDS and
              have MySQL as the DS used by my project. I assume
              that sort of strategy isn't terribly uncommon

              If I knew more about hsqldb perhaps I'd just stick
              with it. Maybe that should be my project for today...

              • 4. Re: mySQL adequate for jbossmq?
                Serge Brisson Newbie

                On JBoss 3.2.1 with MySQL 4.0.13, I am using:

                DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES USING JMS_MESSAGES AS M, JMS_TRANSACTIONS AS T WHERE M.TXID=T.TXID AND M.TXOP=?

                Serge

                • 5. Re: mySQL adequate for jbossmq?
                  quasimodal Newbie


                  > Am I foolish to try to use mySQL as the db behind
                  > my J2EE applications? Is there a better way for me
                  > to help jbossmq make friends with mysql? Any other
                  > gotchas I should be watching for?
                  >
                  > Thanks in advance for help.

                  If you're comfortable with MySQL, I'd stay with it. I tend to use PostgreSQL when given the chance to, I find MySQL a pain to work with.

                  • 6. Re: mySQL adequate for jbossmq?
                    dsalmen Newbie

                    Why not just:

                    delete from jms_messages where txop = ? and txid is not null

                    The idea is that if the "txid" is not null, then by definition the the specified txid is a foreign key to the jms_transactions table (i.e., it would meet the subquery looking for txid's in the jms_transaction table).

                    This is the "fastest" solution - significantly faster than the subquery on jms_transaction when the number of transactions gets large. Admittedly, it does cut the edge a bit :-).

                    I was forced to find a solution to this because I am using Mysql 3.23.55 and upgraded to JBoss 3.2.1.

                    Interested in your thoughts on this.

                    Thx,
                    dave s

                    • 7. Re: mySQL adequate for jbossmq?
                      Adrian Brock Master

                      Although it is a foreign key, it is not enforced because
                      the txid on the message is not cleared after the transaction
                      completes (for speed).

                      This is why it was changed to a subselect.

                      I have a contribution where somebody suggested the following
                      SQL - I haven't tested it yet, it uses a delete through a join

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

                      Regards,
                      Adrian