3 Replies Latest reply on May 31, 2007 10:02 AM by diet ice

    Bug?: 4.2GA & MySQL => SpyJMSException

    diet ice Newbie

      Hello,

      I have a problem with JBoss 4.2GA and MySQL 5 (v5.0.41). When I startup JBoss AS i get the following;

      14:04:42,204 INFO [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=DefaultDS' to JNDI name 'java:DefaultDS'
      14:04:42,861 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: (com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Every derived table must have its own alias)
       at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:495)
       at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1809)
       at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
       at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
       at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
       at $Proxy0.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:417)
       at org.jboss.system.ServiceController.start(ServiceController.java:435)
       at org.jboss.system.ServiceController.start(ServiceController.java:435)
       at org.jboss.system.ServiceController.start(ServiceController.java:435)
       at org.jboss.system.ServiceController.start(ServiceController.java:435)
       at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
       at $Proxy4.start(Unknown Source)
       at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
       at $Proxy42.start(Unknown Source)
       at org.jboss.deployment.XSLSubDeployer.start(XSLSubDeployer.java:197)
       at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
       at sun.reflect.GeneratedMethodAccessor19.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
       at $Proxy9.deploy(Unknown Source)
       at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:421)
       at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:634)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
       at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:336)
       at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
       at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
       at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
       at $Proxy0.start(Unknown Source)
       at org.jboss.system.ServiceController.start(ServiceController.java:417)
       at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
       at $Proxy4.start(Unknown Source)
       at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
       at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
       at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
       at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
       at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
       at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
       at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
       at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
       at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
       at $Proxy5.deploy(Unknown Source)
       at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
       at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
       at org.jboss.Main.boot(Main.java:200)
       at org.jboss.Main$1.run(Main.java:508)
       at java.lang.Thread.run(Thread.java:619)
      Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Every derived table must have its own alias
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
       at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
       at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
       at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:236)
       at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:427)
       ... 111 more
      14:04:42,914 INFO [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../deploy/jmx-console.war/
      14:04:43,044 ERROR [URLDeploymentScanner] Incomplete Deployment listing:
      
      --- MBeans waiting for other MBeans ---
      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: (com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Every derived table must have its own alias)
       I Depend On:
       jboss.jca:service=DataSourceBinding,name=DefaultDS
       Depends On Me:
       jboss.mq:service=DestinationManager
      
      --- 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: (com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Every derived table must have its own alias)
       I Depend On:
       jboss.jca:service=DataSourceBinding,name=DefaultDS
       Depends On Me:
       jboss.mq:service=DestinationManager
      
      
      14:04:43,502 INFO [Http11Protocol] Starting Coyote HTTP/1.1 on http-127.0.0.1-8080
      14:04:43,733 INFO [AjpProtocol] Starting Coyote AJP/1.3 on ajp-127.0.0.1-8009
      14:04:43,778 INFO [Server] JBoss (MX MicroKernel) [4.2.0.GA (build: SVNTag=JBoss_4_2_0_GA date=200705111440)] Started in 17s:967ms

      To reproduce this simply copy the file jboss-4.2.0.GA/docs/examples/jca/mysql-ds.xml into the deploy dir and adapt it.

      I searched around but all I found were jira issues for older versions (e.g. http://jira.jboss.com/jira/browse/JBAS-2424 that should be fixed now.

      Last but not least it works flawlessly with 4.0.5.

      So: Am I completely missing something or is this a bug? I would really appreciate any help because I'm quite lost with it currently and have to rely on 4.2GA & MySQL 5.

      Thanks a lot in advance!

        • 1. Re: Bug?: 4.2GA & MySQL => SpyJMSException
          diet ice Newbie

          Bump!

          Am I really the only one who is using MySQL? Or, asked in an other way: Is anyone using MySQL 5.0.x together with JBoss 4.2.0 GA without getting this warning? If so, please post your datasource configuration.

          @JBoss Devs: *PLEASE* have a look at this! I spend the last days looking for a solution but found nothing - except the JIRA issue mentioned in my first post.

          If I don't get a response I'll open a new issue but I really don't think that I'm the only one who's using MySQL?!

          Again: To repoduce this just replace /server/default/deploy/hsqldb-ds.xml with /docs/examples/jca/mysql-ds.xml, adapt it to your configuration, startup the server and you will see this exception (even with a clean installation and NO applications installed).

          Last but not least it's probably worth mentioning that the connection is working because the necessary tables get created but I'm still worried why this is happening!

          • 2. Re: Bug?: 4.2GA & MySQL => SpyJMSException
            Peter Johnson Master

            Here is the problem. The SQL statements in the files server/xxx/deploy/jms/hsqldb-jdbc2-service.xml and server/xxx/deploy/jms/hsqldb-jdbc-state-service.xml are geared towards HSQLDB. You need to replace these files with the MySQL equivalent. There is a wiki page concerning this at http://wiki.jboss.org/wiki/Wiki.jsp?page=ConfigJBossMQDB.

            Personally, I prefer to use JBoss Messaging, in which case the source code for JBoss Messaging comes with the proper replacement files (though I have not yet tried them on 4.2).

            • 3. Re: Bug?: 4.2GA & MySQL => SpyJMSException
              diet ice Newbie

              Thank you very much for your response!

              I just had to copy mysql-jdbc2-service.xml and change MySqlDS into DefaultDS. Because there was no example mysql-jdbc-state-service.xml I used the hsql one and now it's starting without any errors.

              Thanks again for your help!