mySQL adequate for jbossmq?
tom.purvis Jun 18, 2003 5:48 PMI'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.