How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?

[mannuyi]

I have to ask someone who can help me.

I spent whole day, but I still failed.

Below is my step by step:

1.Replace deploy\hsqldb-ds.xml with mysql-ds.xml

<datasources>

<local-tx-datasource>

   <jndi-name>DefaultDS</jndi-name>

   <connection-url>jdbc:mysql://localhost:3306/localDB</connection-url>

   <driver-class>com.mysql.jdbc.Driver</driver-class>

   <user-name>root</user-name>

   <password></password>

   <min-pool-size>5</min-pool-size>

   <max-pool-size>20</max-pool-size>

   <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

   <idle-timeout-minutes>0</idle-timeout-minutes>

   <track-statements/>

   <prepared-statement-cache-size>32</prepared-statement-cache-size>

   <metadata>

      <type-mapping>mySQL</type-mapping>

   </metadata>

</local-tx-datasource>

</datasources>

2.deploy\jbossesb.esb\message-store-ds.xml

<datasources>

   <local-tx-datasource>

         <jndi-name>JBossESBDS</jndi-name>

         <connection-url>jdbc:mysql://localhost:3306/jbossesb</connection-url>

         <driver-class>com.mysql.jdbc.Driver</driver-class>

         <user-name>mysql</user-name>

         <password></password>

         <min-pool-size>5</min-pool-size>

         <max-pool-size>20</max-pool-size>

         <idle-timeout-minutes>0</idle-timeout-minutes>

         <prepared-statement-cache-size>32</prepared-statement-cache-size>

         <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

   </local-tx-datasource>

</datasources>

3.deploy\jbossesb.esb\jbossesb-service.xml

<server>

   <mbean code="org.jboss.internal.soa.esb.dependencies.DatabaseInitializer"

       name="jboss.esb:service=MessageStoreDatabaseInitializer">

      <attribute name="Datasource">java:/JBossESBDS</attribute>

      <attribute name="ExistsSql">select count(*) from message</attribute>

      <attribute name="SqlFiles">

         message-store-sql/mysql/create_database.sql

      </attribute>

      <depends>jboss.jca:service=DataSourceBinding,name=JBossESBDS</depends>

   </mbean>

</server>

4.deploy\jbossesb-registry.sar\juddi-ds.xml

<datasources>

   <local-tx-datasource>

         <jndi-name>juddiDB</jndi-name>

         <connection-url>jdbc:mysql://localhost:3306/juddiDB</connection-url>

         <driver-class>com.mysql.jdbc.Driver</driver-class>

         <user-name>root</user-name>

         <password></password>

         <min-pool-size>5</min-pool-size>

         <max-pool-size>20</max-pool-size>

         <idle-timeout-minutes>0</idle-timeout-minutes>

         <prepared-statement-cache-size>32</prepared-statement-cache-size>

         <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

         <!--<depends>jboss:service=Hypersonic,database=juddiDB</depends>-->

   </local-tx-datasource>

</datasources>

5.deploy\jbossesb-registry.sar\META-INF\persistence.xml

......

	<properties>
	<property name="hibernate.archive.autodetection" value="class"/>
	<property name="hibernate.hbm2ddl.auto" value="update"/>
	<property name="hibernate.show_sql" value="false"/>
	<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" />
	</properties>

......

6.Rename deploy\jboss-messaging.sar\hsqldb-persistence-service.xml to mysql-persistence-service.xml

7.deploy\jbpm.esb\jbpm-ds.xml

<datasources>

   <local-tx-datasource>

         <jndi-name>JbpmDS</jndi-name>

         <connection-url>jdbc:mysql://localhost:3306/jbpmDB</connection-url>

         <driver-class>com.mysql.jdbc.Driver</driver-class>

         <user-name>root</user-name>

         <password></password>

         <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

         <min-pool-size>5</min-pool-size>

         <max-pool-size>20</max-pool-size>

         <idle-timeout-minutes>0</idle-timeout-minutes>

         <prepared-statement-cache-size>32</prepared-statement-cache-size>

   </local-tx-datasource>

</datasources>

8.deploy\jbpm.esb\hibernate.cfg.xml

......

<property name="hibernate.dialect">org.hibernate.dialect.mysqlDialect</property>

	<!-- JDBC connection properties (begin) ===-->
	<property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
	<property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmDB</property>
	<property name="hibernate.connection.username">root</property>
	<property name="hibernate.connection.password"></property>

......

9.deploy\jbpm.esb\jbpm-quartz.properties

......

org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.MySQLDelegate

......

10.deploy\jbpm.esb\jbpm-service.xml

......

	<attribute name="SqlFiles">
	jbpm-sql/jbpm.jpdl.mysql.sql,
	jbpm-sql/import.sql
	</attribute>

......

11.Download mysql.jar, and copy to lib folder.

12.create databases and grant privileges:

mysqladmin -u root create localDB

mysqladmin -u root create jbossesb

mysqladmin -u root create juddiDB

mysqladmin -u root create jbpmDB

grant all privileges on *.* to ''@'localhost' identified by '';

13.start run.bat

14.Exception log

2011-07-03 23:47:13,406 DEBUG [org.jboss.system.ServiceController] starting service jboss.messaging:service=PersistenceManager

2011-07-03 23:47:13,406 DEBUG [org.jboss.messaging.core.jmx.JDBCPersistenceManagerService] Starting jboss.messaging:service=PersistenceManager

2011-07-03 23:47:15,156 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] Failed to execute: CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, HEADERS LONGVARBINARY, PAYLOAD LONGVARBINARY, PRIMARY KEY (MESSAGE_ID))

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'LONGVARBINARY, PAYLOAD LONGVARBINARY, PRIMARY KEY (MESSAGE_ID))' at line 1

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)

    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)

    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)

[tcunning]

6.Rename deploy\jboss-messaging.sar\hsqldb-persistence-service.xml to mysql-persistence-service.xml

Did you replace hsqldb-persistence-service.xml with the MySQL version, or just rename the file?     It definitely looks like the problem is with this step.

[mannuyi]

Hi, Tom

I just rename the file from hsqldb-persistence-service.xml to mysql-persistence-service.xml.

As you say, how can I replace hsqldb-persistence-service.xml with the MySQL version?

By the way, a friend of mine told me that mysql cannot excute a lot of SQLs since version 4, for example:

create table JBPM_ACTION(......) type=InnoDB

So he suggested me to use mysql under version 4, or to use other database, such as oracle.

What's your suggestion?

Thanks.

[mannuyi]

I just tried to use Oracle10g.

I only replace deploy\hsqldb-ds.xml with oracle-ds.xml

<datasources>
   <local-tx-datasource>

      <jndi-name>DefaultDS</jndi-name>

      <connection-url>jdbc:oracle:thin:@192.168.0.1:1521:localDB</connection-url>

      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>

      <user-name>simon</user-name>

      <password>simon</password>

      <min-pool-size>5</min-pool-size>

      <max-pool-size>20</max-pool-size>

      <idle-timeout-minutes>0</idle-timeout-minutes>

      <track-statements/>

      <prepared-statement-cache-size>32</prepared-statement-cache-size>

      <metadata>
         <type-mapping>Oracle</type-mapping>
      </metadata>

   </local-tx-datasource>

</datasources>

There's an exception occured,

2011-07-05 17:37:49,591 DEBUG [org.jboss.messaging.core.impl.JDBCSupport] Failed to execute: CREATE TABLE JBM_MSG_REF (MESSAGE_ID BIGINT, CHANNEL_ID BIGINT, TRANSACTION_ID BIGINT, STATE CHAR(1), ORD BIGINT, PAGE_ORD BIGINT, DELIVERY_COUNT INTEGER, SCHED_DELIVERY BIGINT, PRIMARY KEY(MESSAGE_ID, CHANNEL_ID))
java.sql.SQLException: ORA-00902: invalid datatype

Because oracle doesnot support BIGINT, so I have to modify the create SQL?

Where's the file?

Thanks.

[mannuyi]

Hi, Tom

Did you replace hsqldb-persistence-service.xml with the MySQL version, or just rename the file?     It definitely looks like the problem is with this step.

->

The following is my hsqldb-persistence-service.xml file, there's no such SQL:

CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, HEADERS LONGVARBINARY, PAYLOAD LONGVARBINARY, PRIMARY KEY (MESSAGE_ID))

<?xml version="1.0" encoding="UTF-8"?>

<!--
     Hypersonic persistence deployment descriptor.
    
     DO NOT USE HYPERSONIC IN PRODUCTION or in a clustered environment- Hypersonic does not have transaction isolation

     $Id: hsqldb-persistence-service.xml 3339 2007-11-16 20:48:08Z [e-mail] $
-->

<server>

   <!-- Persistence Manager MBean configuration
       ======================================= -->
  
   <mbean code="org.jboss.messaging.core.jmx.JDBCPersistenceManagerService"
      name="jboss.messaging:service=PersistenceManager"
      xmbean-dd="xmdesc/JDBCPersistenceManager-xmbean.xml">
     
      <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
     
      <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
           
      <!-- The datasource to use for the persistence manager -->
                               
      <attribute name="DataSource">java:/DefaultDS</attribute>
     
      <!-- If true will attempt to create tables and indexes on every start-up -->
                 
      <attribute name="CreateTablesOnStartup">true</attribute>
     
      <!-- If true then will use JDBC batch updates -->
                
      <attribute name="UsingBatchUpdates">false</attribute>
     
      <!-- The maximum number of parameters to include in a prepared statement -->           
     
      <attribute name="MaxParams">500</attribute>
   </mbean>

   <!-- Messaging Post Office MBean configuration
        ========================================= -->
  
   <mbean code="org.jboss.messaging.core.jmx.MessagingPostOfficeService"
      name="jboss.messaging:service=PostOffice"     
      xmbean-dd="xmdesc/MessagingPostOffice-xmbean.xml">
           
      <depends optional-attribute-name="ServerPeer">jboss.messaging:service=ServerPeer</depends>
     
      <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
     
      <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
     
      <!-- The name of the post office -->     
                
      <attribute name="PostOfficeName">JMS post office</attribute>
     
      <!-- The datasource used by the post office to access it's binding information -->                      
     
      <attribute name="DataSource">java:/DefaultDS</attribute>
     
      <!-- If true will attempt to create tables and indexes on every start-up -->
                       
      <attribute name="CreateTablesOnStartup">true</attribute>
     
      <!-- This post office is NON CLUSTERED - do not use clustering with Hypersonic!! -->
     
      <attribute name="Clustered">false</attribute>
   </mbean>

   <!-- Messaging JMS User Manager MBean config
        ======================================= -->
  
   <mbean code="org.jboss.jms.server.plugin.JDBCJMSUserManagerService"
      name="jboss.messaging:service=JMSUserManager"
      xmbean-dd="xmdesc/JMSUserManager-xmbean.xml">
     
      <depends>jboss.jca:service=DataSourceBinding,name=DefaultDS</depends>
     
      <depends optional-attribute-name="TransactionManager">jboss:service=TransactionManager</depends>
     
      <attribute name="DataSource">java:/DefaultDS</attribute>
     
      <attribute name="CreateTablesOnStartup">true</attribute>
     
      <attribute name="SqlProperties"><![CDATA[
POPULATE.TABLES.1  = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('guest', 'guest')
POPULATE.TABLES.2  = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('j2ee', 'j2ee')
POPULATE.TABLES.3  = INSERT INTO JBM_USER (USER_ID, PASSWD, CLIENTID) VALUES ('john', 'needle', 'DurableSubscriberExample')
POPULATE.TABLES.4  = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('nobody', 'nobody')
POPULATE.TABLES.5  = INSERT INTO JBM_USER (USER_ID, PASSWD) VALUES ('dynsub', 'dynsub')
POPULATE.TABLES.6  = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('guest','guest')
POPULATE.TABLES.7  = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('j2ee','guest')
POPULATE.TABLES.8  = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('john','guest')
POPULATE.TABLES.9  = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('subscriber','john')
POPULATE.TABLES.10 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','john')
POPULATE.TABLES.11 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('publisher','dynsub')
POPULATE.TABLES.12 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','john')
POPULATE.TABLES.13 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('durpublisher','dynsub')
POPULATE.TABLES.14 = INSERT INTO JBM_ROLE (ROLE_ID, USER_ID) VALUES ('noacc','nobody')
      ]]></attribute>
   </mbean>

</server>