0 Replies Latest reply on Jan 12, 2009 4:43 AM by Ashok Sharma

    Error while switching from HSQLDB to MySQL

    Ashok Sharma Newbie

      Hi All,
      i want to use mysql database for esb instead of using hsqldb. So i am replacing files step by step.
      In first step i have modified "...\jbossesb-server-4.4.GA\server\default\deploy\jbossesb.sar\juddi-ds.xml" to make it suitable for mysql and restarted the service successfully. 31 tables are created in "juddidb" which i had created in mysql database.

      Then in second step i replaced ""...\jbossesb-server-4.4.GA\server\default\deploy\hsqldb-ds.xml" available in " with "mysql-ds.xml" taken from "...\jbossesb-server-4.4.GA\docs\examples\jca" and modified for mysql. As soon as i put the "mysql-ds.xml" i start getting below exception in loop.

      Could any one help me to understand the reason of this exception and help me to find the solution?

      Below are the exception and my "mysql-ds.xml"

      14:51:23,984 INFO [TomcatDeployer] undeploy, ctxPath=/esbconfig, warUrl=.../tmp/deploy/tmp45508jbossesb-config-editor-exp.war/
      14:51:24,109 WARN [JDBCSupport] SQLException caught, SQLState 42000 code:1064- assuming deadlock detected, try:4
      com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL se
      ver version for the right syntax to use near 'CONDITION, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES ('JMS post office'' at line 1
       at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
       at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
       at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
       at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
      


      <?xml version="1.0" encoding="UTF-8"?>
      
      <!-- $Id: mysql-ds.xml 63175 2007-05-21 16:26:06Z rrajesh $ -->
      <!-- Datasource config for MySQL using 3.0.9 available from:
      http://www.mysql.com/downloads/api-jdbc-stable.html
      -->
      
      <datasources>
       <local-tx-datasource>
       <jndi-name>DefaultDS</jndi-name>
       <connection-url>jdbc:mysql://127.0.0.1:3306/localDB</connection-url>
       <driver-class>com.mysql.jdbc.Driver</driver-class>
       <user-name>test</user-name>
       <password>test</password>
       <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
       <!-- should only be used on drivers after 3.22.1 with "ping" support
       <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
       -->
       <!-- sql to call when connection is created
       <new-connection-sql>some arbitrary sql</new-connection-sql>
       -->
       <!-- sql to call on an existing pooled connection when it is obtained from pool - MySQLValidConnectionChecker is preferred for newer drivers
       <check-valid-connection-sql>some arbitrary sql</check-valid-connection-sql>
       -->
      
       <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml -->
       <metadata>
       <type-mapping>mySQL</type-mapping>
       </metadata>
       </local-tx-datasource>
      </datasources>