mySQL Persistence Manager / JMS Problems

[ablynch]

Hi

I've followed instructions to make mysql the DefaultDS in Jboss but I'm having problems with jms. I created the mysql-ds.xml file and copied the mysql-jdbc2-service.xml file from examples to the default/deploy/jms directory.

***PROBLEM
The persistence manager does not start. When I start Jboss all the tables are created. JMS_USERS, JMS_MESSAGES, JMS_TRANSACTIONS etc. But it falters on some other SQL statement (apparently because of syntax, see the log below).

***OTHER CHANGES
In mysql-jdbc2-service.xml I changed
"CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER, PRIMARY KEY (TXID))"

to

"CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS (TXID INTEGER)"

I changed this because I was getting a SQL syntax error via JBOSS that TXID needed to be NOT NULL as it was a primary key.

I also changed "INTERVAL" to "INTERVALS" and "other" to "text" in ejb-deployer.xml so that the timer table could be created.

I'm not sure if these changes that I've made are causing my current problem or not (they fixed the problems I was facing at the time), but I thought I should let you know.

***LOG
ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed jboss.mq:service=PersistenceManager
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 near 'JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_T' at line 1")

***mysql-jdbc2-service.xml

<depends optional-attribute-name="ConnectionManager">jboss.jca:service=DataSourceBinding,name=DefaultDS

***mysql-ds.xml

<local-tx-datasource>
<jndi-name>DefaultDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
<driver-class>com.mysql.jdbc.Driver</driver-class>
<user-name>jboss</user-name>
password

<!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->

<type-mapping>mySQL</type-mapping>

</local-tx-datasource>


***standardjaws.xml

java:/DefaultDS
<type-mapping>mySQL</type-mapping>

***standardjbosscmp-jdbc.xml
<jbosscmp-jdbc>

java:/DefaultDS
<datasource-mapping>mySQL</datasource-mapping>

***Specs
J Connector = mysql-connector-java-3.0.11-stable
MySQL = 3.2.1
JBOSS = 4.0.0
OS = Windows XP

***Help?
If you have any ideas about this problem please let me know.
Do I need j connector 3.0.9?
Do I need to upgrade to a later release (RC??) of JBOSS?
Could this be anything to do with privileges of the jboss user in mysql? I used grant all but I'm not positive that it worked?

[darranl]

Did you remove the file 'hsqldb-jdbc2-service.xml' from the jms folder?

[ablynch]

Thanks for your reply, this problem is killing me.

"darranl" wrote:
Did you remove the file 'hsqldb-jdbc2-service.xml' from the jms folder?

Yes, I did.

Well I haven't had much progress, but this is what I've come across.

In the mysql-jdbc2-service.xml file under the PersistenceManager mbean, there is a line:
"DELETE_MARKED_MESSAGES_WITH_TX = DELETE JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_TRANSACTIONS.TXID AND JMS_MESSAGES.TXOP=?"

This seems to be the line of code that is causing the errors in the log. I think it is a syntax problem. I dumped this truncated version of this sql into a normal sql query (outside of Jboss in MySQLManager):
"DELETE JMS_MESSAGES FROM JMS_MESSAGES"
This sql statement didn't work. It threw an syntax exception (with not much explanation). So this seems to be the problem.

Maybe I have stuffed up in one of the mapping files and it's not converting the sql to mysql-specific sql. Does that make sense?

Or maybe my version of mysql is too old. I'm contemplating getting 4.1 ... But I'm not confident that will fix the problem because I don't think (well I'm guessing) that the syntax of DELETE statements hasn't changed much in the past 100 years if you catch my drift. I read somewhere (I forget where) that mysql 4.1 is the first mysql release to handle nested sql or something along those lines, and I *think* I remember reading that this was somehow related to my current problem.

Please reply if you have any ideas!

[ablynch]

From MySQL 4.0, you can specify multiple tables in the DELETE statement to delete rows from one or more tables depending on a particular condition in multiple tables

How about that, DELETE syntax has changed.

Seems that my version of mysql does not support delete statements which reference more than 1 table.

I hope that mysql 4.1.7 fixes my problems.

[ablynch]

It did fix my problem.

If you're using jboss 4.0 or later with mysql, you should probably use mysql 4.0 or later.