Error using MySQL 4.1 with JBoss AS 4.0.4GA

[zerrt]

I am using JBoss AS 4.0.4 GA
mysql 4.1.21
connector driver: mysql-connector-java-3.1.12-bin
Windows XP Pro Service Pack 2

The server runs fine with the default hypersonic DB, but when I try to use my mysql database I get the following error on startup:

2006-08-27 17:46:21,031 WARN [org.jboss.system.ServiceController] Problem starting service jboss.mq:service=PersistenceManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: Every derived table must have its own alias)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:442)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1571)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
...
...
Caused by: java.sql.SQLException: Every derived table must have its own alias
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2994)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:936)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1030)
at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:90)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:403)

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.mq:service=PersistenceManager
State: FAILED
Reason: org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (java.sql.SQLException: Every derived table must hav
I Depend On:
jboss.jca:service=DataSourceBinding,name=DefaultDS
Depends On Me:
jboss.mq:service=DestinationManager

Is this version of mysql not compatible? Any help would be greatly appreciated.

[georgesberscheid]

Change the following line in server/default/deploy/jms/hsqldb-jdbc2-service.xml (this is also where you probably changed the datasource from the DefaultDS to your MySqlDS)

from:

SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES)

to:

SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_MESSAGES

[zerrt]

Thanks, it is working now after this change.