JBPM 3.2 on tomcat which uses mysql - LOGINI UNSUCCESSFUL me

[ygiriyap]

Trying to use JBPM 3.2 on tomcat which uses mysql - login unsuccessful

After entering ernie/ernie ...
Infact .. unable to login using any of the listed username/passwd combinations.

I am getting the following messgae on the page.

Hmmmm... Check you caps lock, put on your glasses and try again.
Please, use any of the following username and password combinations.

I have all the user,group and membership tables popluated properly as in the default hsqldb.

Any pointers ...

Thanks,
--Yash

[kukeltje]

did you configure tomcat to use this database and these specific user table for authentication?

[ygiriyap]

Yes ... I have done the following things ....

1. I have all the user,group and membership tables popluated properly in mysql db as in the default hsqldb. (this seems to be fine for me)

2. I have configured tomcat to use the mysql db.
In the file ... WEB-INF\classes\hibernate.cfg.xml ... following modifications were made.

<!-- hibernate dialect -->
org.hibernate.dialect.MySQLInnoDBDialect

<!-- JDBC connection properties (begin) -->
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/jbpm32test
root
admin
<!-- JDBC connection properties (end) -->

org.hibernate.cache.HashtableCacheProvider

<!-- DataSource properties (begin) -->
java:/JbpmDS
<!-- DataSource properties (end) -->

Pls let me know if the hibernate properties are configured properly.
Do I need a Datasource as mentioned above?(wan not there for jbpm 3.1.4)

AND KINDLY LET ME KNOW THE RIGHT MINIMAL LIST OF THE JAR FILES NEEDED TO GET THIS WORKING.

[ygiriyap]

the hibernate entries are as below ...

 <!-- hibernate dialect -->
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</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/jbpm32test</property>
 <property name="hibernate.connection.username">root</property>
 <property name="hibernate.connection.password">admin</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) -->

Thanks,
--Yash

[kukeltje]

shouting does not help.... au contraire...

I did not ask if you configured the database correctly, I asked if you configured Tomcat (not jBPM) to use this for authentication. In 3.1 jBPM used it's own mechanism, in 3.2 the authentication is left to the container.

[mputz]

Please see below the necessary steps to get jBPM 3.2 working with Tomcat and MySQL.

Environment:
Tomcat 6.0.10
MySQL 5.0.15
jBPM 3.2.GA

1. in jbpm-jpdl-3.2.GA/deploy run

ant customize.console.for.tomcat

2. this builds a jbpm-console.war in jbpm-jpdl-3.2.GA/deploy/customized (almost) ready for deployment in TC

3. change the jbpm-console.war/WEB-INF/classes/hibernate.cfg.xml to reflect the following changes:

<hibernate-configuration>
 <session-factory>

 <!-- hibernate dialect -->
 <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</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/jbpm32</property>
 <property name="hibernate.connection.username">jbossjbpm</property>
 <property name="hibernate.connection.password">jbossjbpm</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) -->
 <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
...

(Make sure to use the hibernate.connection.driver/url attributes to connect to the db and not the hibernate.connection.datasource property. Also set hibernate.transaction.factory_class to JDBCTransactionFactory instead of the JTATransactionFactory or CMTTransactionFactory which are already in this file.)

4. copy jboss-j2ee.jar (or maybe as well any jar that contains the default javax.transaction package) to jbpm-console.war/WEB-INF/lib. (Note: jboss-j2ee.jar can be found in a standard JBoss AS distribution)

5. Copy the jar containing the mysql jdbc driver to /$CATALINA_HOME/lib

6. Copy the adapted jbpm-console.war to /$CATALINA_HOME/webapps

Give it a try and start Tomcat. You should be able to start the jbpm web application, but for now you cannot successfully log in.

Next thing to do would be to properly configure your security realm. You could either go for the standard file based MemoryRealm and just add the jBPM specific users and roles to /$CATALINA_HOME/conf/tomcat-users.xml, OR...

Setup a JDBCRealm:

7. create a file jbpm-console.xml in /$CATALINA_HOME/conf/Catalina/localhost similar to

<Context>
<Realm className="org.apache.catalina.realm.JDBCRealm"
 driverName="com.mysql.jdbc.Driver"
 connectionURL="jdbc:mysql://localhost:3306/jbpm32"
 connectionName="jbossjbpm"
 connectionPassword="jbossjbpm"
 userTable="JBPM_ID_USER"
 userNameCol="NAME_"
 userCredCol="PASSWORD_"
 userRoleTable="JBPM_ID_MEMBERSHIP"
 roleNameCol="ROLE_" />
</Context>

8. Adapt the jBPM user tables to fit in the Tomcat Realm specification. If you have the standard tables (JBPM_ID_USER, JBPM_ID_GROUP, JBPM_ID_MEMBERSHIP) with the default entries already, be sure that the the columns NAME_ and ROLE_ in JBPM_ID_MEMBERSHIP are not NULL. If so you could use the following SQL to update this table:

UPDATE jbpm_id_membership j, jbpm_id_user u
SET j.NAME_ = u.NAME_
WHERE j.USER_ = u.ID_

UPDATE jbpm_id_membership j, jbpm_id_group g
SET j.ROLE_ = g.NAME_
WHERE j.GROUP_ = g.ID_

Now you should be able to run jBPM default web app in Tomcat and login with the username/password from the db.

[kukeltje]

great... many thanks... Especially that login part. Not many people know that and think it is (unrightfully) not their 'problem'.

btw, Could you make a wiki page out of this...

[ygiriyap]

Hi Martin,

Superb .... Many thanks.
I could get it going smoothly now.

--Yash

[kukeltje]

I put a link to this post in the wiki http://wiki.jboss.org/wiki/Wiki.jsp?page=JbpmOnTomcat and this is referenced from the server compatibility page.

[hipchic]

Wich script have use for setup the database just the <jbpm-home>\db\jbpm.jpdl.mysql.sql ?

i try to setup my environment but i have a database error , and i currently using the tomcat 5.5 with oracle 10g xe.

15:38:41,921 [JbpmJobExector:216.94.110.165:1] WARN JDBCExceptionReporter : SQL Error: 923, SQLState: 42000
15:38:41,931 [JbpmJobExector:216.94.110.165:1] ERROR JDBCExceptionReporter : ORA-00923: FROM keyword not found where expected

15:38:41,931 [JbpmJobExector:216.94.110.165:1] ERROR JobSession : org.hibernate.exception.SQLGrammarException: could not execute query
15:38:41,951 [JbpmJobExector:216.94.110.165:1] ERROR JobExecutorThread : exception in job executor thread. waiting 80000 milliseconds
org.jbpm.JbpmException: couldn't get acquirable jobs
at org.jbpm.db.JobSession.getFirstAcquirableJob(JobSession.java:44)
at org.jbpm.job.executor.JobExecutorThread.acquireJobs(JobExecutorThread.java:111)
at org.jbpm.job.executor.JobExecutorThread.run(JobExecutorThread.java:56)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query

thks for your reply

my original post http://www.jboss.com/index.html?module=bb&op=viewtopic&t=105211

[mputz]

Ronald,

I've finally managed to get the wiki entry up. Feel free to make any additions/modifications in case I forgot something.

Regards,
Martin

[kukeltje]

Cool, thanks. Sorry I did not do it myself. I rather busy answering all these questions in the forum. Although I'd rather work on the wiki, faq etc, since that will minimize duplicate questions in the forum (I hope)

Thanks again,

[wjm]

As a final note on this topic I'd like to point out that the easiest way to create the jbpm database, for any given database server, is to let hibernate do the job, and not rely on SQL at all as the solution proposes. This includes creation of the Identity components (which were the primary point of confusion here) also the user/group/memberships from "Sesame Street" which are necessary for the default jbpm-console to work "out of the box" under Tomcat, Jetty, Resin or any other configuration.

I've created a new wiki page which shows how to do this, completely independent of any SQL script, and for any database supported by Hibernate.

See here:

http://wiki.jboss.org/wiki/Wiki.jsp?page=UseHibernateToCreateJbpmDB


----
Bill

[kukeltje]

A real final note: http://jira.jboss.com/jira/browse/JBPM-561

[kukeltje]

I forgot to add a 'thank you', but still one issue remains. populating the identity tables....