3 Replies Latest reply on Jun 21, 2012 1:08 PM by Matteo Mancini

    Switching database to MySQL in JBoss AS 5.1 for JBossESB 4.9

    Max Werner Newbie

      Hey folks!

       

      I installed JBoss AS 5.1 on my Win7 plattform and deployed JBossESB 4.9 to it as decribed in this turorial:

      https://community.jboss.org/wiki/Step-by-StepGuideToIntegrateJBossESB49IntoJBossAS510

       

      Everything worked fine and i run through the getting started guide of JBoss ESB and successfully deployed helloworld.esb.

       

      After some reading of the Installation-and-getting-started guide and Administration guide i thought it might be good to change the Datasource DB from Hypersonic to MySQL 5 (it is recommended by these guides)

      So i followed the instruction of the Administration Guide of the JBoss ESB which is shipped with JBoss ESB 4.9. (http://docs.jboss.org/jbossesb/docs/4.10/manuals/html/Administration_Guide/index.html#Switching_Databases Chapter 1.5.1)

       

      Everthing went fine accept that i get allot of "DEPLOYMENT MISSING DEPENDENCIES" and i just can't figure out why!

      It seems that jbossesb.esb is not deployed correctly and that causes the upcomming errors.

       

      The output of the shell when i start the JBoss AS is as follows:

       

      DEPLOYMENTS MISSING DEPENDENCIES:
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbossesb.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbossesb.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:service=JuddiClient' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbpm.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbpm.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbrules.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbrules.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/slsb.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/slsb.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/smooks.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/smooks.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/soap.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/soap.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/spring.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/spring.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb.vfszip:/C:/Max/java_utilities/jboss5/server/default/deploy/HelloWorld.esb/" is missing the following dependencies:
          Dependency "<UNKNOWN jboss.esb.vfszip:/C:/Max/java_utilities/jboss5/server/default/deploy/HelloWorld.esb/>" (should be in state "PreInstall", but is actually in state "** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **")
        Deployment "jboss.esb:service=JuddiClient" is missing the following dependencies:
          Dependency "jboss.esb:service=JuddiRMI" (should be in state "Create", but is actually in state "Configured")
        Deployment "jboss.esb:service=JuddiRMI" is missing the following dependencies:
          Dependency "jboss.esb:service=JUDDIDatabaseInitializer" (should be in state "Create", but is actually in state "** NOT FOUND Depends on 'jboss.esb:service=JUDDIDatabaseInitializer' **")
      
      
      DEPLOYMENTS IN ERROR:
        Deployment "<UNKNOWN jboss.esb.vfszip:/C:/Max/java_utilities/jboss5/server/default/deploy/HelloWorld.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "jboss.esb:service=JUDDIDatabaseInitializer" is in error due to the following reason(s): ** NOT FOUND Depends on 'jboss.esb:service=JUDDIDatabaseInitializer' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/spring.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/slsb.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/smooks.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbossesb.esb/>" is in error due to the following reason(s): **
      UNRESOLVED Demands 'jboss.esb:service=JuddiClient' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbpm.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/jbrules.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
        Deployment "<UNKNOWN jboss.esb.vfsfile:/C:/Max/java_utilities/jboss5/server/default/deploy/soap.esb/>" is in error due to the following reason(s): ** UNRESOLVED Demands 'jboss.esb:deployment=jbossesb.esb' **
      
      

       

       

      I appreciate any help to solve this!

       

      Thanks allot in advance!

       

      P.S.: as attatchment my boot.log and server.log

        • 1. Re: Switching database to MySQL in JBoss AS 5.1 for JBossESB 4.9
          Wolf-Dieter Fink Master

          I suppose a problem with JUDDI service.

          You may have made a mistake when changing the config file.

          • 2. Re: Switching database to MySQL in JBoss AS 5.1 for JBossESB 4.9
            Max Werner Newbie

            First of all thank you for your qiuck response!

            I'm new to jboss and i'm pretty much lost when it comes to config files within jboss. I don't know where to start allocating my mistake.

            Could you please guide me to important config files to look at? I'd appriciate that!

             

            What i did was

            1. change the DefaultDS from Hypersonic to MySQL by removing the hsqldb-ds.xml file with mysql-ds.xml in the server/default/deploy directory

             

            mysql-ds.xml:

            <?xml version="1.0" encoding="UTF-8"?>
            
            
            <!-- See http://www.jboss.org/community/wiki/Multiple1PC for information about local-tx-datasource -->
            <!-- $Id: mysql-ds.xml 88948 2009-05-15 14:09:08Z jesper.pedersen $ -->
            <!--  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://localhost:3306/defaultdb</connection-url>
                <driver-class>com.mysql.jdbc.Driver</driver-class>
                <user-name>someUser</user-name>
                <password>somePassword</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>select count(*) from jbm_user</check-valid-connection-sql>
            
            
            
                <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
                <metadata>
                   <type-mapping>mySQL</type-mapping>
                </metadata>
              </local-tx-datasource>
            </datasources>
            
            

             

            2. replaced the configuration of deploy/jbossesb-regitry.sar/juddi-ds.xml to:

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            
            
            <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>someUser</user-name>
                                <password>somePasswd</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>select count(*) from j3_publisher</check-valid-connection-sql>
            
            
            
                                <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
                                <metadata>
                                   <type-mapping>mySQL</type-mapping>
                                </metadata>
                      </local-tx-datasource>
            </datasources>
            
            

             

            3.  replaced the configuration of deploy/jbossesb.esb/message-store-ds.xml to:

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            
            
            <datasources>
               <local-tx-datasource>
                                <jndi-name>JBossESBDS</jndi-name>
                                <!-- use for in-process db -->
                                <connection-url>jdbc:mysql://localhost:3306/jbossesbdb</connection-url>
                                <driver-class>com.mysql.jdbc.Driver</driver-class>
                                <user-name>someUser</user-name>
                                <password>somePassowrd</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>select count(*) from j3_publisher</check-valid-connection-sql>
                                 --> 
            
            
                                <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml (optional) -->
                                <metadata>
                                   <type-mapping>mySQL</type-mapping>
                                </metadata>
                      </local-tx-datasource>
               <!-- use for in-process db 
               <mbean code="org.jboss.jdbc.HypersonicDatabase" 
                    name="jboss:service=Hypersonic,database=jbossesb">
                    <attribute name="Database">jbossesb</attribute>
                    <attribute name="InProcessMode">true</attribute>
               </mbean>
            </datasources>
            
            

             

            4.  edid the configuration of deploy/jbossesb.esb/jbossesb-service.xml to:

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            
            
            <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>
            
            

             

            5. change the dialect to MySQL5Dialect /deploy/jbossesb-registry.sar/juddi_config/META-INF/persistence.xml:

             

             

            ...
            ...
            <properties>
                  <property name="hibernate.archive.autodetection" value="class"/>
                  <property name="hibernate.show_sql" value="false"/>
            
            
                  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect" />
                </properties>
            
            

             

            6. and replaced the hsqldb-persistance-service.xml with mysql-persistance-service.xml in /deploy/messaging

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            
            
            <!--
                 MySql persistence deployment descriptor.
            
            
                 Tested with MySQL 5.0.27
            
            
                 $Id$
             -->
            
            
            <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 we will automatically detect and reject duplicate messages sent during failover -->
            
            
                  <attribute name="DetectDuplicates">true</attribute>
            
            
                  <!-- The size of the id cache to use when detecting duplicate messages -->
            
            
                  <attribute name="IDCacheSize">500</attribute>
            
            
                  <attribute name="SqlProperties"><![CDATA[
               CREATE_DUAL=CREATE TABLE JBM_DUAL (DUMMY INTEGER, PRIMARY KEY (DUMMY)) ENGINE = INNODB
               CREATE_MESSAGE_REFERENCE=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)) ENGINE = INNODB
               CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE) 
               CREATE_MESSAGE=CREATE TABLE JBM_MSG (MESSAGE_ID BIGINT, RELIABLE CHAR(1), EXPIRATION BIGINT, TIMESTAMP BIGINT, PRIORITY TINYINT, TYPE TINYINT, HEADERS MEDIUMBLOB, PAYLOAD LONGBLOB, PRIMARY KEY (MESSAGE_ID)) ENGINE = INNODB
               CREATE_TRANSACTION=CREATE TABLE JBM_TX (NODE_ID INTEGER, TRANSACTION_ID BIGINT, BRANCH_QUAL VARBINARY(254), FORMAT_ID INTEGER, GLOBAL_TXID VARBINARY(254), PRIMARY KEY (TRANSACTION_ID)) ENGINE = INNODB
               CREATE_COUNTER=CREATE TABLE JBM_COUNTER (NAME VARCHAR(255), NEXT_ID BIGINT, PRIMARY KEY(NAME)) ENGINE = INNODB
               CREATE_ID_CACHE=CREATE TABLE JBM_ID_CACHE (NODE_ID INTEGER, CNTR INTEGER, JBM_ID VARCHAR(255), PRIMARY KEY(NODE_ID, CNTR)) ENGINE = INNODB
               INSERT_DUAL=INSERT INTO JBM_DUAL VALUES (1)
               CHECK_DUAL=SELECT 1 FROM JBM_DUAL
               INSERT_MESSAGE_REF=INSERT INTO JBM_MSG_REF (CHANNEL_ID, MESSAGE_ID, TRANSACTION_ID, STATE, ORD, PAGE_ORD, DELIVERY_COUNT, SCHED_DELIVERY) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
               DELETE_MESSAGE_REF=DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
               UPDATE_MESSAGE_REF=UPDATE JBM_MSG_REF SET TRANSACTION_ID=?, STATE='-' WHERE MESSAGE_ID=? AND CHANNEL_ID=? AND STATE='C'
               UPDATE_PAGE_ORDER=UPDATE JBM_MSG_REF SET PAGE_ORD = ? WHERE MESSAGE_ID=? AND CHANNEL_ID=?
               COMMIT_MESSAGE_REF1=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+'
               COMMIT_MESSAGE_REF2=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='-'
               ROLLBACK_MESSAGE_REF1=DELETE FROM JBM_MSG_REF WHERE TRANSACTION_ID=? AND STATE='+'
               ROLLBACK_MESSAGE_REF2=UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='-'
               LOAD_PAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, PAGE_ORD, SCHED_DELIVERY FROM JBM_MSG_REF WHERE CHANNEL_ID = ? AND PAGE_ORD BETWEEN ? AND ? ORDER BY PAGE_ORD
               LOAD_UNPAGED_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? AND PAGE_ORD IS NULL ORDER BY ORD
               LOAD_REFS=SELECT MESSAGE_ID, DELIVERY_COUNT, SCHED_DELIVERY FROM JBM_MSG_REF WHERE STATE = 'C' AND CHANNEL_ID = ? ORDER BY ORD
               UPDATE_REFS_NOT_PAGED=UPDATE JBM_MSG_REF SET PAGE_ORD = NULL WHERE PAGE_ORD BETWEEN ? AND ? AND CHANNEL_ID=?
               SELECT_MIN_MAX_PAGE_ORD=SELECT MIN(PAGE_ORD), MAX(PAGE_ORD) FROM JBM_MSG_REF WHERE CHANNEL_ID = ?
               UPDATE_DELIVERY_COUNT=UPDATE JBM_MSG_REF SET DELIVERY_COUNT = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
               UPDATE_CHANNEL_ID=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE CHANNEL_ID = ?
               MOVE_REFERENCE=UPDATE JBM_MSG_REF SET CHANNEL_ID = ? WHERE MESSAGE_ID = ? AND CHANNEL_ID = ?
               LOAD_MESSAGES=SELECT MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, HEADERS, PAYLOAD, TYPE FROM JBM_MSG
               INSERT_MESSAGE=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
               INSERT_MESSAGE_CONDITIONAL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE) SELECT ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
               UPDATE_MESSAGE_4CONDITIONAL=UPDATE JBM_MSG SET HEADERS=?, PAYLOAD=? WHERE MESSAGE_ID=?
               INSERT_MESSAGE_CONDITIONAL_FULL=INSERT INTO JBM_MSG (MESSAGE_ID, RELIABLE, EXPIRATION, TIMESTAMP, PRIORITY, TYPE, HEADERS, PAYLOAD) SELECT ?, ?, ?, ?, ?, ?, ?, ? FROM JBM_DUAL WHERE NOT EXISTS (SELECT MESSAGE_ID FROM JBM_MSG WHERE MESSAGE_ID = ?)
               MESSAGE_ID_COLUMN=MESSAGE_ID
               DELETE_MESSAGE=DELETE FROM JBM_MSG WHERE MESSAGE_ID = ? AND NOT EXISTS (SELECT JBM_MSG_REF.MESSAGE_ID FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = ?)
               INSERT_TRANSACTION=INSERT INTO JBM_TX (NODE_ID, TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID) VALUES(?, ?, ?, ?, ?)
               DELETE_TRANSACTION=DELETE FROM JBM_TX WHERE NODE_ID = ? AND TRANSACTION_ID = ?
               SELECT_PREPARED_TRANSACTIONS=SELECT TRANSACTION_ID, BRANCH_QUAL, FORMAT_ID, GLOBAL_TXID FROM JBM_TX WHERE NODE_ID = ?
               SELECT_MESSAGE_ID_FOR_REF=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '+' ORDER BY ORD
               SELECT_MESSAGE_ID_FOR_ACK=SELECT MESSAGE_ID, CHANNEL_ID FROM JBM_MSG_REF WHERE TRANSACTION_ID = ? AND STATE = '-' ORDER BY ORD
               UPDATE_COUNTER=UPDATE JBM_COUNTER SET NEXT_ID = ? WHERE NAME=?
               SELECT_COUNTER=SELECT NEXT_ID FROM JBM_COUNTER WHERE NAME=? FOR UPDATE
               INSERT_COUNTER=INSERT INTO JBM_COUNTER (NAME, NEXT_ID) VALUES (?, ?)
               SELECT_ALL_CHANNELS=SELECT DISTINCT(CHANNEL_ID) FROM JBM_MSG_REF
               UPDATE_TX=UPDATE JBM_TX SET NODE_ID=? WHERE NODE_ID=?
               UPDATE_ID_IN_CACHE=UPDATE JBM_ID_CACHE SET JBM_ID = ? WHERE NODE_ID = ? AND CNTR = ?
               INSERT_ID_IN_CACHE=INSERT INTO JBM_ID_CACHE (NODE_ID, CNTR, JBM_ID) VALUES (?, ?, ?)
               LOAD_ID_CACHE=SELECT CNTR, JBM_ID FROM JBM_ID_CACHE WHERE NODE_ID = ?
                  ]]></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>
            
            
                  <attribute name="SqlProperties"><![CDATA[
            CREATE_POSTOFFICE_TABLE=CREATE TABLE JBM_POSTOFFICE (POSTOFFICE_NAME VARCHAR(255), NODE_ID INTEGER, QUEUE_NAME VARCHAR(255), COND VARCHAR(1023), SELECTOR VARCHAR(1023), CHANNEL_ID BIGINT, CLUSTERED CHAR(1), ALL_NODES CHAR(1), PRIMARY KEY(POSTOFFICE_NAME, NODE_ID, QUEUE_NAME)) ENGINE = INNODB
            INSERT_BINDING=INSERT INTO JBM_POSTOFFICE (POSTOFFICE_NAME, NODE_ID, QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            DELETE_BINDING=DELETE FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=? AND QUEUE_NAME=?
            LOAD_BINDINGS=SELECT QUEUE_NAME, COND, SELECTOR, CHANNEL_ID, CLUSTERED, ALL_NODES FROM JBM_POSTOFFICE WHERE POSTOFFICE_NAME=? AND NODE_ID=?
                  ]]></attribute>
            
            
                  <!-- This post office is non clustered. If you want a clustered post office then set to true -->
                  
                  <attribute name="Clustered">false</attribute>
            
            
                  <!-- All the remaining properties only have to be specified if the post office is clustered.
                       You can safely comment them out if your post office is non clustered -->
            
            
                  <!-- The JGroups group name that the post office will use -->
            
            
                  <!--attribute name="GroupName">${jboss.messaging.groupname:MessagingPostOffice}</attribute-->
            
            
                  <!-- Max time to wait for state to arrive when the post office joins the cluster -->
            
            
                  <!--attribute name="StateTimeout">30000</attribute-->
            
            
                  <!-- Max time to wait for a synchronous call to node members using the MessageDispatcher -->
            
            
                  <!--attribute name="CastTimeout">30000</attribute-->
                  
                  <!-- Set this to true if you want failover of connections to occur when a node is shut down -->
                  
                  <!--attribute name="FailoverOnNodeLeave">false</attribute-->
                                <!--
                  <depends optional-attribute-name="ChannelFactoryName">jboss.jgroups:service=ChannelFactory</depends>
                  <attribute name="ControlChannelName">jbm-control</attribute>
                  <attribute name="DataChannelName">jbm-data</attribute>
                  <attribute name="ChannelPartitionName">${jboss.partition.name:DefaultPartition}-JMS</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[
            CREATE_USER_TABLE=CREATE TABLE JBM_USER (USER_ID VARCHAR(32) NOT NULL, PASSWD VARCHAR(32) NOT NULL, CLIENTID VARCHAR(128), PRIMARY KEY(USER_ID)) ENGINE = INNODB
            CREATE_ROLE_TABLE=CREATE TABLE JBM_ROLE (ROLE_ID VARCHAR(32) NOT NULL, USER_ID VARCHAR(32) NOT NULL, PRIMARY KEY(USER_ID, ROLE_ID)) ENGINE = INNODB
            SELECT_PRECONF_CLIENTID=SELECT CLIENTID FROM JBM_USER WHERE USER_ID=?
            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>
            
            

             

            I hope this helps to allocate the mistake

            • 3. Re: Switching database to MySQL in JBoss AS 5.1 for JBossESB 4.9
              Matteo Mancini Newbie

              Hi,

              try to append this at the end of your juddi-ds.xml:

               

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

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

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

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

                      <attribute name="SqlFiles">juddi-sql/mysql/import.sql</attribute>

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

                  </mbean>