7 Replies Latest reply on Mar 18, 2008 6:32 AM by Martin Weiler

    Problem with changing backend database to mysql

    David Ling Newbie

      Hi,

      I am having some problems with changing the backend database to MySQL.

      I have already installed MySQL and changed all the xml files below

      * jbpm-ds.xml
      * login-config.xml
      * standardjaws.xml
      * hibernate.cfg.xml

      Now the server "seems" to start up fine and I can get to http://localhost:8080/jbpm-console/sa/processes.jsf . However, when I type in any valid username and password (manager-manager), the same URL remains and it now gives me a "HTTP Status 403 - Access to the requested resource has been denied" error.

      I know this is a very broad error message and it could mean a lot of things, but if you have experienced this before and managed to fix it I would appreciate it if you can help me out.

      I am using the following
      * jbpm-suite-3.2.2
      * MySQL 4.1
      * mysql-connector-java-5.0.4-bin.jar

      Thanks for your help.

        • 1. Re: Problem with changing backend database to mysql
          David Ling Newbie

          Sorry I should have posted more details in my first message.

          In jbpm-ds.xml I have changed the code to the following

          <?xml version="1.0" encoding="UTF-8"?>
          
          <datasources>
          
           <local-tx-datasource>
           <jndi-name>JbpmDS</jndi-name>
           <connection-url>jdbc:mysql://localhost:3306/jbpmbackend</connection-url>
           <driver-class>com.mysql.jdbc.Driver</driver-class>
           <user-name>root</user-name>
           <password>password</password>
           <metadata>
           <type-mapping>MySQL</type-mapping>
           </metadata>
           </local-tx-datasource>
          
          </datasources>
          


          In login-config.xml I have added the following to the end of the file
          <application-policy name = "jbpm">
           <authentication>
           <login-module code="org.jboss.security.auth.spi.DatabaseServerLoginModule"
           flag="required">
           <module-option name="dsJndiName">java:/JbpmDS</module-option>
           <module-option name="principalsQuery">
           SELECT PASSWORD_ FROM JBPM_ID_USER WHERE NAME_=?
           </module-option>
           <module-option name="rolesQuery">
           SELECT g.NAME_ ,'Roles'
           FROM JBPM_ID_USER u,
           JBPM_ID_MEMBERSHIP m,
           JBPM_ID_GROUP g
           WHERE g.TYPE_='security-role'
           AND m.GROUP_ = g.ID_
           AND m.USER_ = u.ID_
           AND u.NAME_=?
           </module-option>
           </login-module>
           </authentication>
           </application-policy>
          


          In standardjaws.xml I have changed it to
          <jaws>
           <datasource>java:/JbpmDS</datasource>
           <type-mapping>mySQL</type-mapping>
           <debug>false</debug>
           ......
          


          In the hibernate.cfg.xml file, I have changed it to the following:
          <?xml version='1.0' encoding='utf-8'?>
          
          <!DOCTYPE hibernate-configuration PUBLIC
           "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
           "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
          
          <hibernate-configuration>
           <session-factory>
          
           <!-- hibernate dialect -->
           <!--<property name="hibernate.dialect">org.hibernate.dialect.HSQLDialect</property> -->
           <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
          
           <!-- JDBC connection properties (begin) -->
           <!--<property name="hibernate.connection.driver_class">org.hsqldb.jdbcDriver</property> -->
           <!--<property name="hibernate.connection.url">jdbc:hsqldb:mem:jbpm</property> -->
           <!--<property name="hibernate.connection.username">sa</property> -->
           <!--<property name="hibernate.connection.password"></property> -->
           <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
           <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmbackend</property>
           <property name="hibernate.connection.username">root</property>
           <property name="hibernate.connection.password">password</property>
           <!-- JDBC connection properties (end) -->
          
           <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
          
           <!-- DataSource properties (begin) ==
           <property name="hibernate.connection.datasource">java:/JbpmDS</property>
           == DataSource properties (end) -->
          
           <!-- JTA transaction properties (begin) -->
           <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
           <property name="hibernate.transaction.manager_lookup_class">org.hibernate.transaction.JBossTransactionManagerLookup</property>
           <!-- JTA transaction properties (end) -->
          


          Any help would be appreciated.

          Thanks

          • 2. Re: Problem with changing backend database to mysql
            Martin Weiler Apprentice

            First, if you really want to use the datasource as defined in jbpm-ds.xml, you should alter hibernate.cfg.xml to resemble this:

             <!-- DON'T USE DIRECT HIBERNATE CONFIGS, USE DATASOURCE INSTEAD -->
             <!--
             <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
             <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/jbpmbackend</property>
             <property name="hibernate.connection.username">root</property>
             <property name="hibernate.connection.password">password</property>
             -->
             <!-- JDBC connection properties (end) -->
             <property name="hibernate.cache.provider_class">org.hibernate.cache.HashtableCacheProvider</property>
             <!-- DataSource properties (begin) --> <property name="hibernate.connection.datasource">java:/JbpmDS</property> <!-- DataSource properties (end) -->


            Next, do you have users in your database? What does this SQL query return?
            SELECT PASSWORD_ FROM JBPM_ID_USER WHERE NAME_='manager'


            • 3. Re: Problem with changing backend database to mysql
              David Ling Newbie

              Hi Martin,

              Yep, there's data in the database. Currently if I type that query, the result is manager.

              I have just uncommented the hibernate.connection.datasource and redeployed it, but it is still coming up with the same error.

              Thanks

              David

              • 4. Re: Problem with changing backend database to mysql
                David Ling Newbie

                Hi,

                Just wondering if there are a specific set of instructions that shows how to change the backend database to MySQL, preferrably for version 3.2.2.

                I find http://docs.jboss.com/jbpm/v3/userguide/thejbpmdatabase.html to be slightly out of date ... correct me if I am wrong though.

                Thanks

                David

                • 5. Re: Problem with changing backend database to mysql
                  Martin Weiler Apprentice

                  True, the docs are not completely matching the 3.2 version, as the paths mentioned in this chapter still refer to the 3.1.x starters kit. But in general, the process of switching the DB backend is still roughly the same.

                  Now, back to your initial issue. I'm wondering if your config changes are really getting picked up. In the server.log you should see similar messages:

                  5785:2008-03-11 13:09:36,054 INFO [org.hibernate.cfg.Configuration] configuring from resource: hibernate.cfg.xml
                  5786:2008-03-11 13:09:36,054 INFO [org.hibernate.cfg.Configuration] Configuration resource: hibernate.cfg.xml
                  5790:2008-03-11 13:09:36,165 DEBUG [org.hibernate.cfg.Configuration] hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
                  5791:2008-03-11 13:09:36,165 DEBUG [org.hibernate.cfg.Configuration] hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider
                  5792:2008-03-11 13:09:36,165 DEBUG [org.hibernate.cfg.Configuration] hibernate.connection.datasource=java:/JbpmDS
                  


                  of course in your case for mysql.

                  Also, check if you have multiple hibernate.cfg.xml files somewhere in your deployment, also check inside archives as well.

                  • 6. Re: Problem with changing backend database to mysql
                    David Ling Newbie

                    Hi Martin,

                    Sorry for the late reply, but the solution you suggested works. Now I can hook up MySQL and try and use SeeWhy as well.

                    Thanks for your help

                    • 7. Re: Problem with changing backend database to mysql
                      Martin Weiler Apprentice

                      David,

                      thanks for the feedback! Glad to hear that my comments helped.

                      All the best for your further jBPM adventures!