5 Replies Latest reply on Dec 1, 2004 3:01 AM by Alexey Loubyansky

    problem with jboss 4.0.0 and mysql 4.0.21

    alexis solanas Newbie

      hi everybody

      i'm trying to configure jboss and mysql, and this is the problem i have :

      ERROR [DatabasePersistencePolicy] Starting failed jboss.ejb:persistencePolicy=database,service=EJBTimerService
      java.sql.SQLException: Syntax error or access violation message from server: "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 'INTERVAL bigint,
      INSTANCEPK other,
      INFO o"
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
      at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2244)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:2192)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259)
      at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205)
      at org.jboss.resource.adapter.jdbc.WrappedStatement.executeUpdate(WrappedStatement.java:229)
      at org.jboss.ejb.txtimer.DatabasePersistencePolicy.createTableIfNotExists(DatabasePersistencePolicy.java:403)
      at org.jboss.ejb.txtimer.DatabasePersistencePolicy.startService(DatabasePersistencePolicy.java:102)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:271)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:172)
      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:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.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:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:908)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:416)
      at org.jboss.system.ServiceController.start(ServiceController.java:438)
      at org.jboss.system.ServiceController.start(ServiceController.java:438)
      at org.jboss.system.ServiceController.start(ServiceController.java:438)
      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.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.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:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:261)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:935)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:746)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:709)
      at sun.reflect.GeneratedMethodAccessor30.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:119)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:131)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
      at $Proxy8.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:305)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:481)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:204)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:277)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:271)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:221)
      at sun.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.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:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:891)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:416)
      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.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.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:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:261)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:935)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:746)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:709)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:693)
      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:324)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:141)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:80)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:119)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:131)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:74)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:242)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:642)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:176)
      at $Proxy5.deploy(Unknown Source)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:396)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:293)
      at org.jboss.Main.boot(Main.java:151)
      at org.jboss.Main$1.run(Main.java:405)
      at java.lang.Thread.run(Thread.java:534)

      and so on ...



      this are the steps i followed to configure jboss :

      i created a database in mysql called jboss, and granted all permissions to myuser.

      i copied mysql-connector-java-3.0.15-ga-bin.jar to {jboss}/server/default/lib

      and i deleted all hsql datasources from {jboss}/server/default/deploy



      These are the configuration files i have now.

      {jboss}/server/default/deploy/mysql-ds.xml


      <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/jboss</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>myuser</user-name>
      mypassword
      </local-tx-datasource>




      {jboss}/server/default/deploy/jbosscmp-jdbc.xml

      <jbosscmp-jdbc>


      java:/DefaultDS
      <datasource-mapping>mySQL</datasource-mapping>
      <create-table>true</create-table>
      <remove-table>false</remove-table>
      <read-only>false</read-only>
      .....

      {jboss}/server/default/conf/jbosscmp-jdbc.xml


      <jbosscmp-jdbc>

      java:/DefaultDS
      <datasource-mapping>mySQL</datasource-mapping>
      .....


      {jboss}/server/default/conf/standardjbosscmp-jdbc.xml

      <jbosscmp-jdbc>

      java:/DefaultDS
      <datasource-mapping>mySQL</datasource-mapping>
      .....


      {jboss}/server/default/conf/standardjaws.xml


      java:/DefaultDS
      <type-mapping>mySQL</type-mapping>
      false
      .....


      what else do i have to configure in order to connect properly mysql and jboss ??

        • 1. Re: problem with jboss 4.0.0 and mysql 4.0.21
          Mark Newbie

          I already posted about this problem and a few others a little while ago. So far no comprehensive reply, which unfortunately is rather normal here.

          I managed to get past your particular problem by replacing INTERVAL by TIMER_INTERVAL and the type 'other' by 'text'. The reason being that the SQL in the file is not standard and MySQL chokes on it. But then I had other problems that prevented me to go on after this, so I went back to 3.2.x

          • 2. Re: problem with jboss 4.0.0 and mysql 4.0.21
            Pete Newbie

            Hi there,

            I have EXACTLY the same problem. I just tried 4.0.1RC1, and this time, I got a slightly different problem:

            00:00:48,420 ERROR [DatabasePersistencePolicy] Starting failed jboss.ejb:persistencePolicy=database,service=EJBTimerService
            java.sql.SQLException: Syntax error or access violation message from server: "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 'INTERVAL BIGINT, INSTANCEPK LONGBLOB, INFO LONGBLOB, constra"
            at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
            at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
            at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
            at com.mysql.jdbc.MysqlIO.sqlQuery(MysqlIO.java:1224)
            at com.mysql.jdbc.Connection.execSQL(Connection.java:2244)
            at com.mysql.jdbc.Connection.execSQL(Connection.java:2192)
            at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1259)
            at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1205)
            ........

            So the ejb-deployer.xml is different from the one in 4.0.0, but I am still getting the SQL syntax issue. Anyone has any clue?

            Pete

            • 3. Re: problem with jboss 4.0.0 and mysql 4.0.21
              Martin Thoma Newbie

              The problem is not occurring with JBoss 4.0.0RC2. If you don't need the Timer Service, you can either swith to 4.0.0RC2, or delete the Timer Service related elements in ejb-deploy.xml (or something similar, I'm not sure about the name) in the deploy directory.

              • 4. Re: problem with jboss 4.0.0 and mysql 4.0.21
                Alec Lynch Newbie

                Yeh I overcame the initial problem that was raised by changing INTERVAL to INTERVALS. Apparently INTERVAL is a reserved word in MySQL. The timer problem is no longer an issue for me. But I seem to have endless problems with JMS stuff.

                I have two questions:
                - Do I need JMS to have CMP?
                - If I'm on 4.0.0 (first release) how do I upgrade to RC2? Do I have to download all 70 MB of it?

                Sorry if they're stupid questions, I'm new here.

                Cheers

                • 5. Re: problem with jboss 4.0.0 and mysql 4.0.21
                  Alexey Loubyansky Master

                  - You don't need JMS for CMP.
                  - Yes, download all the Mbs.