Problem with changing backend database to mysql

[david_ling]

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.

[david_ling]

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

[mputz]

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'

[david_ling]

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

[david_ling]

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

[mputz]

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.

[david_ling]

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

[mputz]

David,

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

All the best for your further jBPM adventures!