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

    problem with jboss 4.0.0 and mysql 4.0.21

    alexis_solanas
    alexis_solanas Oct 9, 2004 2:43 PM

      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 ??

      • 3673 Views
      • Tags:
        • 1. Re: problem with jboss 4.0.0 and mysql 4.0.21
          tesuji
          tesuji Oct 9, 2004 4:42 PM (in response to alexis_solanas)

          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

            • Actions
          • 2. Re: problem with jboss 4.0.0 and mysql 4.0.21
            thebadpete
            thebadpete Nov 9, 2004 11:21 AM (in response to alexis_solanas)

            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

              • Actions
            • 3. Re: problem with jboss 4.0.0 and mysql 4.0.21
              mthoma
              mthoma Nov 11, 2004 7:23 AM (in response to alexis_solanas)

              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.

                • Actions
              • 4. Re: problem with jboss 4.0.0 and mysql 4.0.21
                ablynch
                ablynch Nov 29, 2004 7:13 AM (in response to alexis_solanas)

                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

                  • Actions
                • 5. Re: problem with jboss 4.0.0 and mysql 4.0.21
                  aloubyansky
                  aloubyansky Dec 1, 2004 3:01 AM (in response to alexis_solanas)

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

                    • Actions