2 Replies Latest reply on Sep 25, 2003 9:37 PM by ramana_ramana

    Error on configure Jboss 3.2.1 to Mysql 4.0.14b

    panco

      what I am trying to do is to use mySQL as the Default DataSource, but it fails to create the JMS_TRANSACTION table.... I can't figure out what's wrong with the setting, can anyone help?

      I am using Windows XP and software version that I used are:
      jsdk = 1.4.1_05
      j2ee = 1.4 beta2
      jboss = 3.2.1 with Tomcat 4.1.24
      mysql = 4.0.14b

      DataSource Configuration that i made:
      --------------------------
      mysql configure
      ---------------
      1. enable transaction support
      [WinMySQLadmin]
      Server=C:/mysql/bin/mysqld-max-nt.exe

      2. create JBOSSDB
      create database jbossdb;
      grant all privileges on jbossdb.* to admin@localhost identified by '11223344';
      grant all privileges on jbossdb.* to admin identified by '11223344';


      jboss configure
      ---------------
      1. remove Hypersonic DataSource by deleting .\server\default\deploy\hsqldb-ds.xml
      2. copy .\docs\examples\jca\mysql-ds.xml to .\server\default\deploy
      3. edit mysql-ds.xml
      <?xml version="1.0" encoding="UTF-8"?>

      <local-tx-datasource>
      <jndi-name>DefaultDS</jndi-name>
      <connection-url>jdbc:mysql://localhost:3306/jbossdb</connection-url>
      <driver-class>com.mysql.jdbc.Driver</driver-class>
      <user-name>admin</user-name>
      11223344
      </local-tx-datasource>

      4. copy mysql-connector-java-3.0.8-stable-bin.jar to .\server\default\lib
      5. Edit server\default\conf\standardjbosscmp-jdbc.xml
      5a. Change the datasource-mapping from Hypersonic SQL to mySQL.
      5b. Change the value of fk-constraint to true .

      java:/DefaultDS
      <datasource-mapping>mySQL</datasource-mapping>
      :
      (skipped)
      :
      <fk-constraint>true</fk-constraint>
      :
      (skipped)


      6. Edit server\default\conf\standardjaws.xml
      6a. Change the type-mapping from Hypersonic SQL to mySQL.

      java:/DefaultDS
      <type-mapping>mySQL</type-mapping>
      :
      (skipped)


      ======================================
      Error in .\server\default\log

      2003-09-22 10:04:25,718 INFO [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting
      2003-09-22 10:04:25,718 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Resolving uncommited TXS
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] internalRegisterPool: registering pool with interval 900000 old interval: 9223372036854775807
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.connectionmanager.IdleRemover] internalRegisterPool: about to notify thread: old next: 1064196715765, new next: 1064196715765
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Using properties: {user=admin, password=11223344}
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Checking driver for URL: jdbc:mysql://localhost:3306/jbossdb
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver not yet registered for url: jdbc:mysql://localhost:3306/jbossdb
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver not yet registered for url: jdbc:mysql://localhost:3306/jbossdb
      2003-09-22 10:04:25,765 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory] Driver already registered for url: jdbc:mysql://localhost:3306/jbossdb
      2003-09-22 10:04:26,015 DEBUG [org.jboss.mq.pm.jdbc2.PersistenceManager] Could not create table with SQL: CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), MESSAGEBLOB OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) ), got : 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 'OBJECT, PRIMARY KEY (MESSAGEID, DESTINATION) )' at line 1"
      2003-09-22 10:04:26,031 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.parentTraceEnabled=true
      2003-09-22 10:04:26,046 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.nestedTraceEnabled=false
      2003-09-22 10:04:26,046 DEBUG [org.jboss.util.NestedThrowable] org.jboss.util.NestedThrowable.detectDuplicateNesting=true
      2003-09-22 10:04:26,046 WARN [org.jboss.resource.adapter.jdbc.WrappedConnection] Closing a statement you left open, please do your own housekeeping
      2003-09-22 10:04:26,046 ERROR [org.jboss.mq.pm.jdbc2.PersistenceManager] Starting failed
      org.jboss.mq.SpyJMSException: Could not resolve uncommited transactions. Message recovery may not be accurate; - nested throwable: (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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1")
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy11.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      at org.jboss.system.ServiceController.start(ServiceController.java:408)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy5.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
      at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy7.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:200)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:273)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy5.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:597)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy6.deploy(Unknown Source)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:365)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:272)
      at org.jboss.Main.boot(Main.java:150)
      at org.jboss.Main$1.run(Main.java:388)
      at java.lang.Thread.run(Thread.java:536)
      Caused by: 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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1"
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1700)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1569)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:249)
      ... 68 more
      2003-09-22 10:04:26,187 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: 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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1")
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:276)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.startService(PersistenceManager.java:1299)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor56.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy11.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      at org.jboss.system.ServiceController.start(ServiceController.java:408)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy5.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:824)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
      at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy7.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:302)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:476)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:200)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:273)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
      at sun.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:324)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:966)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:392)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy5.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:640)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:613)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:597)
      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.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:549)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
      at $Proxy6.deploy(Unknown Source)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:365)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:272)
      at org.jboss.Main.boot(Main.java:150)
      at org.jboss.Main$1.run(Main.java:388)
      at java.lang.Thread.run(Thread.java:536)
      Caused by: 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 'SELECT TXID FROM JMS_TRANSACTIONS) AND TXOP='A'' at line 1"
      at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
      at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
      at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1700)
      at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1569)
      at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:308)
      at org.jboss.mq.pm.jdbc2.PersistenceManager.resolveAllUncommitedTXs(PersistenceManager.java:249)
      ... 68 more


        • 1. Re: Error on configure Jboss 3.2.1 to Mysql 4.0.14b
          panco

          for Problem solution subject, see the following thread..

          http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=

          • 2. Re: Error on configure Jboss 3.2.1 to Mysql 4.0.14b
            ramana_ramana

            Hi all,

            I have been struggling with JBoss 3.2.1 with MySql 4.1 and have been trying to get them working with "Middlegen" samples. Being a newbie to JBoss, I spent quite a bit of time on getting things to work. I hope the following lessons learnt will be of use to you. Good luck. (I havent tried all the options and havent tried using JMS yet. )


            MySql: version 4.1.0alpha Using INNODB Option

            MySql-Driver: mysql-connector-java-3.0.8-stable-bin.jar in D:\JBoss321\server\default\lib folder

            mysql-ds.xml: Copy this file to D:\JBoss321\server\default\deploy\
            <jndi-name>DefaultDS</jndi-name>
            <!-- I changed this jndi-name and spent hours trying to fix this. If you decide to use DefaultDS, make sure you delete the hsqldb.xml from JBoss321\server\default\deploy folder -->
            <connection-url>jdbc:mysql://127.0.0.1/middlegentest</connection-url>
            <driver-class>org.gjt.mm.mysql.Driver</driver-class>
            <user-name>root1</user-name>
            test

            D:\JBoss321\server\default\conf\standardjbosscmp-jdbc.xml:
            <jbosscmp-jdbc>

            <!-- Used by JMS service for persistance of messages. You have to make changes to JBossmq-service.xml. See below. -->
            java:/DefaultDS
            <!-- Used for type mapping internally. I am not sure if this mapping is case-sensitive -->
            <datasource-mapping>mySQL</datasource-mapping>
            <!-- set to true to create any tables -->
            <create-table>true</create-table>
            .....


            D:\JBoss321\server\default\deploy\jms\jbossmq-service.xml
            Towards the end of the file, you can specify if you want to store the messages on a DB or a file system.
            Using a DB is better.
            To use MySQL with JMS service, a slight change is required in the table creation statement
            <mbean code="org.jboss.mq.pm.jdbc2.PersistenceManager"
            ....................
            CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
            DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER, TXOP CHAR(1), \
            MESSAGEBLOB BLOB, PRIMARY KEY (MESSAGEID, DESTINATION) )
            ...........................

            Change "MESSAGEBLOB OBJECT" to "MESSAGEBLOB BLOB". Otherwise this table creation fails in MySQL