7 Replies Latest reply on Nov 10, 2005 9:57 AM by luc.texier

jms mysql and jboss4.0.3

tomerbd2 Oct 10, 2005 6:43 AM

Hi

I have just upgraded to 4.0.3 from 4.0.2 and i have an error while starting up jboss, im using the jms with mysql, the error does not occur on jboss-4.0.2 and more than that if i use mysql-jdbc2-service.xml of 4.0.3 and i remove the sql's (which is exactly the diff in that file from 4.0.2 to 4.0.3)

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

so if i update these sqls to be the ones in 4.0.2 then everything is ok.

following is the exception i receive on 4.0.3 ver:

12:32:17,187 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultDS'
12:32:17,921 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=ConnectionFactoryBinding,name=JmsXA' to JNDI name 'java:JmsXA'
12:32:18,515 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=DataSourceBinding,name=MASDS' to JNDI name 'java:MASDS'
12:32:18,531 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=DataSourceBinding,name=MASJMSDS' to JNDI name 'java:MASJMSDS'
12:32:19,062 WARN [ServiceController] Problem starting service jboss.mq:service
=PersistenceManager
org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Messag
e recovery may not be accurate; - nested throwable: (java.sql.SQLException: Synt
ax error or access violation, message from server: "Every derived table must ha
ve its own alias")
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(Pers
istenceManager.java:416)
at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceMana
ger.java:1499)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
upport.java:274)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
eanSupport.java:230)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
ler.java:943)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:428)
at org.jboss.system.ServiceController.start(ServiceController.java:446)
at org.jboss.system.ServiceController.start(ServiceController.java:446)
at org.jboss.system.ServiceController.start(ServiceController.java:446)
at org.jboss.system.ServiceController.start(ServiceController.java:446)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:285)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:118)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:127)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.JMXInvocationHandler.invoke(JMXInvocationHandler.ja
va:273)
at $Proxy38.start(Unknown Source)
at org.jboss.deployment.XSLSubDeployer.start(XSLSubDeployer.java:185)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:989)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:790)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:753)
at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:118)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:127)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy9.deploy(Unknown Source)
at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymen
tScanner.java:319)
at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentS
canner.java:507)
at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.
doScan(AbstractDeploymentScanner.java:192)
at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(A
bstractDeploymentScanner.java:265)
at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanS
upport.java:274)
at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMB
eanSupport.java:230)
at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceControl
ler.java:943)
at $Proxy0.start(Unknown Source)
at org.jboss.system.ServiceController.start(ServiceController.java:428)
at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:72)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy4.start(Unknown Source)
at org.jboss.deployment.SARDeployer.start(SARDeployer.java:285)
at org.jboss.deployment.MainDeployer.start(MainDeployer.java:989)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:790)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:753)
at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:737)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.
java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces
sorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatch
er.java:141)
at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractIntercept
or.java:118)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelM
BeanOperationInterceptor.java:127)
at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.
java:245)
at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:644)
at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
at $Proxy5.deploy(Unknown Source)
at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:453)
at org.jboss.system.server.ServerImpl.start(ServerImpl.java:330)
at org.jboss.Main.boot(Main.java:187)
at org.jboss.Main$1.run(Main.java:438)
at java.lang.Thread.run(Thread.java:595)
Caused by: java.sql.SQLException: Syntax error or access violation, message fro
m server: "Every derived table must have its own alias"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1905)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1109)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1203)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2090)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:
1496)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery
(WrappedPreparedStatement.java:211)
at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(Pers
istenceManager.java:384)
... 111 more
12:32:19,375 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=DataSourceBinding,name=MASTestDS' to JNDI name 'java:MASTestDS'
12:32:19,390 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jb
oss.jca:service=DataSourceBinding,name=MASAuditDS' to JNDI name 'java:MASAuditDS
'
12:32:20,531 INFO [EjbModule] Deploying EJBTestRunnerEJB
12:32:20,859 INFO [ProxyFactory] Bound EJB Home 'EJBTestRunnerEJB' to jndi 'ejb
/EJBTestRunner'
12:32:20,859 INFO [EJBDeployer] Deployed: file:/C:/projects/mas/MASCore/common/
tools/jboss-4.0.3/server/default/deploy/mas/junitejb-1.0.jar
12:32:21,281 INFO [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../dep
loy/jmx-console.war/
12:32:21,406 INFO [Server] Runtime shutdown hook called, forceHalt: true
12:32:21,406 INFO [Server] JBoss SHUTDOWN: Undeploying all packages
12:32:21,578 INFO [TomcatDeployer] undeploy, ctxPath=/jmx-console, warUrl=.../d
eploy/jmx-console.war/
12:32:21,656 INFO [EJBDeployer] Undeploying: file:/C:/projects/mas/MASCore/comm
on/tools/jboss-4.0.3/server/default/deploy/mas/junitejb-1.0.jar
12:32:21,656 INFO [EARDeployer] Init J2EE application: file:/C:/projects/mas/MA
SCore/common/tools/jboss-4.0.3/server/default/deploy/mas/mas.ear
12:32:21,718 INFO [ProxyFactory] Unbind EJB Home 'EJBTestRunnerEJB' from jndi '
ejb/EJBTestRunner'
12:32:21,718 INFO [EjbModule] Undeployed EJBTestRunnerEJB
12:32:21,750 INFO [ConnectionFactoryBindingService] Unbound ConnectionManager '
jboss.jca:service=DataSourceBinding,name=MASAuditDS' from JNDI name 'java:MASAud
itDS'
12:32:21,765 INFO [ConnectionFactoryBindingService] Unbound ConnectionManager '
jboss.jca:service=DataSourceBinding,name=MASTestDS' from JNDI name 'java:MASTest
DS'
12:32:21,765 INFO [ConnectionFactoryBindingService] Unbound ConnectionManager '
jboss.jca:service=DataSourceBinding,name=MASJMSDS' from JNDI name 'java:MASJMSDS
'
12:32:21,781 INFO [ConnectionFactoryBindingService] Unbound ConnectionManager '
jboss.jca:service=DataSourceBinding,name=MASDS' from JNDI name 'java:MASDS'

1. Re: jms mysql and jboss4.0.3

clebert.suconic Oct 10, 2005 7:11 AM (in response to tomerbd2)

This is because of this issue:

http://jira.jboss.com/jira/browse/JBAS-2056

I've tested in a MySQL database and it worked, later on I've tested in another database and didn't work. This same query also works on Oracle.

We need a more generic query. Do you have any?

Clebert
Actions
2. Re: jms mysql and jboss4.0.3

tomerbd2 Oct 10, 2005 8:01 AM (in response to tomerbd2)

10x

I'll try the solution...

cant think of a more generic sql sorry.. :/
Actions
3. Re: jms mysql and jboss4.0.3

tomerbd2 Oct 10, 2005 8:27 AM (in response to tomerbd2)

I used

SELECT MAX(TXID) FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) AS TR_TABLE

And i still hav ehte same error :O
Actions
4. Re: jms mysql and jboss4.0.3

toecheez Oct 14, 2005 8:12 PM (in response to tomerbd2)
I was able to get it to werk using the following:

SELECT_MAX_TX = SELECT MAX(TXID) FROM (SELECT * FROM (SELECT MAX(TXID) AS TXID FROM JMS_TRANSACTIONS) AS T UNION SELECT * FROM (SELECT MAX(TXID) AS TXID FROM JMS_MESSAGES) AS M) AS Main

You needed two other aliases to make it all werkerize
Actions
5. Re: jms mysql and jboss4.0.3

dode Oct 16, 2005 6:34 PM (in response to tomerbd2)

When I appended "AS TR_TABLE" to the statement, JBoss just hung on startup. Actually the query just hung.

This may be related to this MySQL bug:
http://bugs.mysql.com/bug.php?id=8726

I had MySQL 4.1.10a and just updated to 4.1.13, now the statement executes normal.

And of course JBoss now also starts fine without any error :-)

Torsten
Actions
6. Re: jms mysql and jboss4.0.3

luc.texier Nov 10, 2005 9:51 AM (in response to tomerbd2)

The missing alias in the sql statement has been fixed in branch40 and head

http://jira.jboss.com/jira/browse/JBAS-2424
Actions
7. Re: jms mysql and jboss4.0.3

luc.texier Nov 10, 2005 9:57 AM (in response to tomerbd2)

Another thing. If you are getting the following exception

14:41:28,307 WARN [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: 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 MAX(TXID) AS TXID FROM JMS_TRANSACTIONS UNION SELECT MAX)

please check out to http://jira.jboss.com/jira/browse/JBAS-2425
Actions

Go to original post