mySQL adequate for jbossmq?

[tom.purvis]

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.

[adrian.brock]

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

[camel]

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.

[tom.purvis]

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...

[sfbriss]

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

[quasimodal]

> 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.

[dsalmen]

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

[adrian.brock]

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