Hello:

I followed the steps outlined in the following wiki documents for changing the backend to use the MySQL database:
http://wiki.jboss.org/wiki/SetUpMysqlAsDefaultDS
http://wiki.jboss.org/wiki/SetUpAMysqlDatasource

I then added some users, groups, and memberships based on the script I stole from here:

http://blogs.dfwikilabs.org/pigui/files/2007/11/mysqlidentityscriptjbpm321sql.txt

After restarting the server, I can log into the jbpm-console with a username & password added to the MySQL database. I am also able to add new groups and users through the jbpm-console. However, I am no longer able to deploy process definitions through the jbpm-console or the gpd.

Looking at the server log, I see the following:

2008-06-20 13:36:03,397 DEBUG [org.hibernate.transaction.JDBCTransaction] begin
2008-06-20 13:36:03,397 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
2008-06-20 13:36:03,397 DEBUG [org.hibernate.transaction.JDBCTransaction] current autocommit status: true
2008-06-20 13:36:03,397 DEBUG [org.hibernate.transaction.JDBCTransaction] disabling autocommit
2008-06-20 13:36:03,397 DEBUG [org.jbpm.persistence.db.DbPersistenceService] begun hibernate transaction org.hibernate.transaction.JDBCTransaction@69bdf8
2008-06-20 13:36:03,397 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2008-06-20 13:36:03,397 DEBUG [org.hibernate.SQL] select top ? processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME3_4_, processdef0_.DESCRIPTION_ as DESCRIPT4_4_, processdef0_.VERSION_ as VERSION5_4_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN6_4_, processdef0_.STARTSTATE_ as STARTSTATE7_4_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc
2008-06-20 13:36:03,397 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() prototypeone -> name [2]
2008-06-20 13:36:03,397 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2008-06-20 13:36:03,397 DEBUG [org.hibernate.util.JDBCExceptionReporter] could not execute query [select processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME3_4_, processdef0_.DESCRIPTION_ as DESCRIPT4_4_, processdef0_.VERSION_ as VERSION5_4_, processdef0_.ISTERMINATIONIMPLICIT_ as ISTERMIN6_4_, processdef0_.STARTSTATE_ as STARTSTATE7_4_ from JBPM_PROCESSDEFINITION processdef0_ where processdef0_.NAME_=? order by processdef0_.VERSION_ desc]
com.mysql.jdbc.exceptions.jdbc4.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 '1 processdef0_.ID_ as ID1_4_, processdef0_.NAME_ as NAME3_4_, processdef0_.DESCR' at line 1

From the looks of it, it may be executing an sql statement that is invalid in MySQL as "SELECT TOP ? col1, col2 FROM tablename WHERE col1 = 'value2'" is invalid in MySQL where as the MySQL equivalent should be "SELECT col1, col2 FROM tablename WHERE col1 = 'value2' LIMIT ?".

I also notice during startup of the JBoss AS, i see the following log info:

13:58:20,085 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- MBeans waiting for other MBeans ---
ObjectName: jboss.ejb:service=EJBTimerService,persistencePolicy=database
 State: CONFIGURED
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS

ObjectName: jboss.mq:service=InvocationLayer,type=HTTP
 State: CONFIGURED
 I Depend On:
 jboss.mq:service=Invoker
 jboss.web:service=WebServer

<more like above and below cut out for brevity>

ObjectName: jboss.mq:service=PersistenceManager
 State: CONFIGURED
 I Depend On:
 jboss.jca:service=DataSourceBinding,name=DefaultDS
 Depends On Me:
 jboss.mq:service=DestinationManager

ObjectName: jboss.mq:service=InvocationLayer,type=UIL2
 State: CONFIGURED
 I Depend On:
 jboss.mq:service=Invoker

--- MBEANS THAT ARE THE ROOT CAUSE OF THE PROBLEM ---
ObjectName: jboss.jca:service=DataSourceBinding,name=DefaultDS
 State: NOTYETINSTALLED
 Depends On Me:
 jboss.ejb:service=EJBTimerService,persistencePolicy=database
 jboss:service=KeyGeneratorFactory,type=HiLo
 jboss.mq:service=StateManager
 jboss.mq:service=PersistenceManager

If someone out there could provide ideas on why the mbean is not being installed/configured that would be much appreciated. Thank you for your time.

Regards,

Richard