1 Reply Latest reply on Jun 21, 2008 2:56 AM by Leo de Blaauw

    MySQL backend

    Richard C Newbie

      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

        • 1. Re: MySQL backend
          Leo de Blaauw Newbie

          Hey,

          Did you just run the suplied ddl script to initialize your MySQL db ? If so change all fields defined as bit type to boolean and you should be ok. This is exactly the problem we had, been running fine ever after since we changed the ddl script.

          Greetz
          Leo