Mapping JBossMQ persistence manager to mysql
geirwilly Jan 16, 2004 5:25 AMYou have an error in your SQL syntax near 'JMS_MESSAGES FROM JMS_MESSAGES, JMS_TRANSACTIONS WHERE JMS_MESSAGES.TXID = JMS_T
I am running jboss 3.2.3, and mysql 3.23.58. using mm.mysql.jar as jdbc-driver.
I want to map the JBossMQ persistence manager to mysql, not using hypersonic db.
I have added a mysql-ds.xml with a datasource named MySqlDS, and copied the mysql-jdbc2-service.xml from docs/examples/jms to deploy/jms.
I was running hsqldb-ds.xml and using DefaultDS as message cache, and found a reply on jboss forums saying:
As from 3.2.1, JBossMQ persistence manager is mapper to the HSQL DB.
I would expect it to be possible to change this mapping.
do I need to add a datasource=MySqlDS with datasource-mapping=mySQL in standardjbosscmp-jdbc.xml.
what about standardjaws.xml (datasource-mapping?)
login-config.xml?
What need to be changed
mysql-ds.xml:
<local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>
<connection-url>
jdbc:mysql://localhost:3306/messagecache
</connection-url>
<driver-class>org.gjt.mm.mysql.Driver</driver-class>
<user-name>username</user-name>
password
</local-tx-datasource>
standardjbosscmp-jdbc.xml
java:/DefaultDS
<datasource-mapping>Hypersonic SQL</datasource-mapping>
<!--<datasource-mapping>mySQL</datasource-mapping>-->
The error on startup:
anager 'jboss.jca:service=LocalTxCM,name=DefaultDS to JNDI name 'java:/DefaultDS'
2004-01-16 11:07:10,151 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=DefaultDS
2004-01-16 11:07:10,171 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.jdbc/JPipeDS] Bound connection factory for resource adapter for Connecti
onManager 'jboss.jca:service=LocalTxCM,name=jdbc/JPipeDS to JNDI name 'java:/jdbc/JPipeDS'
2004-01-16 11:07:10,172 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=jdbc/JPipeDS
2004-01-16 11:07:10,174 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.MySqlDS] Bound connection factory for resource adapter for ConnectionMan
ager 'jboss.jca:service=LocalTxCM,name=MySqlDS to JNDI name 'java:/MySqlDS'
2004-01-16 11:07:10,174 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started jboss.jca:service=LocalTxCM,name=MySqlDS
2004-01-16 11:07:10,571 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
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: 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)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:336)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1356)
at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:976)
at $Proxy14.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:394)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at org.jboss.system.ServiceController.start(ServiceController.java:411)
at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)