mySQL Persistence Manager / JMS Problems
ablynch Nov 29, 2004 12:58 AMHi
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?