4 Replies Latest reply on Jul 6, 2011 5:00 AM by simon shen

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

    simon shen Newbie

      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)

        • 1. Re: How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?
          Tom Cunningham Master

          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.

          • 2. Re: How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?
            simon shen Newbie

            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.

            • 3. Re: How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?
              simon shen Newbie

              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.

              • 4. Re: How to switch JBoss ESB Server 4.7 from HSQLDB to MySQL5.5?
                simon shen Newbie

                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 clebert.suconic@jboss.com $
                -->

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