Replacing Hypersonic with MySQL as DefaultDS

[masj]

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?

[viz]

have you solved this?

[juhalindfors]

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.

[carstens]

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

[carstens]

PS:
same discussion here:
http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=

[rogera]

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.

[masj]

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