7 Replies Latest reply on Aug 26, 2011 5:12 AM by David C

    JBPM v5 & MySQL Persistence Problem

    David C Newbie

      Hi

       

      I've managed to successfully use JBPM v5 persistence with MySQL a while back. I recently removed all the MySQL jbpm tables because I wanted clean the DB, now I am getting problems with creating new JBPM tables in the DB. I've spent a whole day looking for reference about this problem and most of the example persistence.xml and orm.xml files matches what I have. Is there suppose to be a proper way to create the MySQL tables ?

       

      This is the error I am getting

       

      Hibernate: insert into SessionInfo (lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (?, ?, ?, ?)

      java.lang.IllegalStateException: java.lang.reflect.InvocationTargetException

          at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.buildCommanService(KnowledgeStoreServiceImpl.java:130)

          at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.newStatefulKnowledgeSession(KnowledgeStoreServiceImpl.java:54)

          at org.drools.persistence.jpa.JPAKnowledgeService.newStatefulKnowledgeSession(JPAKnowledgeService.java:122)

          at ac.uk.ucl.css.AHECore.Workflow.WorkflowAPI.createKnowledgeSession(WorkflowAPI.java:330)

          at ac.uk.ucl.css.AHECore.Workflow.WorkflowAPI.createWorkflow(WorkflowAPI.java:230)

          at ac.uk.ucl.css.AHECore.Workflow.WorkflowAPI.main(WorkflowAPI.java:75)

      Caused by: java.lang.reflect.InvocationTargetException

          at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

          at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

          at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

          at java.lang.reflect.Constructor.newInstance(Constructor.java:513)

          at org.drools.persistence.jpa.KnowledgeStoreServiceImpl.buildCommanService(KnowledgeStoreServiceImpl.java:116)

          ... 5 more

      Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not insert: [org.drools.persistence.info.SessionInfo]

          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1215)

          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1148)

          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1154)

          at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:678)

          at org.drools.persistence.jpa.JpaPersistenceContext.persist(JpaPersistenceContext.java:17)

          at org.drools.persistence.SingleSessionCommandService.<init>(SingleSessionCommandService.java:125)

          ... 10 more

      Caused by: org.hibernate.exception.SQLGrammarException: could not insert: [org.drools.persistence.info.SessionInfo]

          at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)

          at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

          at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64)

          at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)

          at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)

          at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71)

          at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273)

          at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320)

          at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203)

          at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129)

          at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:69)

          at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:179)

          at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:135)

          at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:61)

          at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:808)

          at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:782)

          at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:786)

          at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:672)

          ... 12 more

      Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'jbpm5db.SessionInfo' doesn't exist

       

       

      This is my persistence.xml

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

      <persistence version="1.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd

      http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd" xmlns:orm="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/persistence">

          <persistence-unit name="org.jbpm.persistence.jpa" transaction-type="JTA">

       

              <provider>org.hibernate.ejb.HibernatePersistence</provider>

       

            

              <jta-data-source>jdbc/testDS1</jta-data-source>

              <mapping-file>META-INF/orm.xml</mapping-file>

              <class>org.drools.persistence.info.SessionInfo</class>

              <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

              <class>org.jbpm.persistence.processinstance.ProcessInstanceEventInfo</class>

              <class>org.drools.persistence.info.WorkItemInfo</class>

              <class>org.jbpm.process.audit.ProcessInstanceLog</class>

              <class>org.jbpm.process.audit.NodeInstanceLog</class>

              <class>org.jbpm.process.audit.VariableInstanceLog</class>      

             

              <class>org.jbpm.task.Task</class>

              <class>org.jbpm.task.Comment</class>

              <class>org.jbpm.task.Attachment</class>

              <class>org.jbpm.task.I18NText</class>

              <class>org.jbpm.task.SubTasksStrategy</class>

              <class>org.jbpm.task.Deadline</class>

              <class>org.jbpm.task.Escalation</class>

              <class>org.jbpm.task.Reassignment</class>

              <class>org.jbpm.task.Notification</class>

              <class>org.jbpm.task.BooleanExpression</class>

              <class>org.jbpm.task.User</class>

              <class>org.jbpm.task.PeopleAssignments</class>

              <properties>

                 

       

                  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>

                  <property name="hibernate.connection.autocommit" value="false"/>

                  <property name="hibernate.max_fetch_depth" value="3"/>

       

                  <property name="hibernate.hbm2ddl.auto" value="create" />

                  <property name="hibernate.show_sql" value="true" />

                  <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.BTMTransactionManagerLookup"/>

                 

                

              </properties>

           

           

          </persistence-unit>

          <persistence-unit name="org.jbpm.task">

              <provider>org.hibernate.ejb.HibernatePersistence</provider>

             

              <class>org.jbpm.task.Attachment</class>

              <class>org.jbpm.task.Content</class>

              <class>org.jbpm.task.BooleanExpression</class>

              <class>org.jbpm.task.Comment</class>

              <class>org.jbpm.task.Deadline</class>

              <class>org.jbpm.task.Comment</class>

              <class>org.jbpm.task.Deadline</class>

              <class>org.jbpm.task.Delegation</class>

              <class>org.jbpm.task.Escalation</class>

              <class>org.jbpm.task.Group</class>

              <class>org.jbpm.task.I18NText</class>

              <class>org.jbpm.task.Notification</class>

              <class>org.jbpm.task.EmailNotification</class>

              <class>org.jbpm.task.EmailNotificationHeader</class>

              <class>org.jbpm.task.PeopleAssignments</class>

              <class>org.jbpm.task.Reassignment</class>

              <class>org.jbpm.task.Status</class>

              <class>org.jbpm.task.Task</class>

              <class>org.jbpm.task.TaskData</class>

              <class>org.jbpm.task.SubTasksStrategy</class>

              <class>org.jbpm.task.OnParentAbortAllSubTasksEndStrategy</class>

              <class>org.jbpm.task.OnAllSubTasksEndParentEndStrategy</class>

       

              <class>org.jbpm.task.User</class>

       

              <class>org.drools.persistence.info.SessionInfo</class>

              <class>org.jbpm.persistence.processinstance.ProcessInstanceInfo</class>

              <class>org.jbpm.persistence.processinstance.ProcessInstanceEventInfo</class>

              <class>org.drools.persistence.info.WorkItemInfo</class>

             

       

              <properties>

                   <!-- sample MySQL configuration  -->

                  

                  <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>

                  <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>

                  <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/jbpm5db" />

                  <property name="hibernate.connection.username" value="test1"/>

                  <property name="hibernate.connection.password" value="test1"/>

               

       

                 

                  <property name="hibernate.connection.autocommit" value="false" />

                  <property name="hibernate.max_fetch_depth" value="3"/>

                  <property name="hibernate.hbm2ddl.auto" value="create" />

                  <property name="hibernate.show_sql" value="true" />

              </properties>

          </persistence-unit>

        • 1. Re: JBPM v5 & MySQL Persistence Problem
          Chris Melas Apprentice

          Hello,

          I'm using mysql and i have dialect property org.hibernate.dialect.MySQLDialect, do you want to give it a try?

          If no success, then please tell me if the table SessionInfo has been created under the database jbpm5db.

          • 2. Re: JBPM v5 & MySQL Persistence Problem
            David C Newbie

            Hi

             

            I've already tried the different MySQL dialect without much success :/

             

            The SessionInfo hasn't been created.

             

            Thanks, Dave

            • 3. Re: JBPM v5 & MySQL Persistence Problem
              Chris Melas Apprentice

              Well then all you have to do is modify (with a schema created in mysql and username/password with full rights on that schema) all the persistence.xml and hibernate.cfg.xml files found in,

              - jbpm-gwt-console-server.war META-INF

              - jbpm-gwt-console-server.war lib/jbpm-human-task-5.1.0.Final.jar META-INF

              - jbpm-gwt-console-server.war lib/jbpm-bam-5.1.0.Final.jar META-INF

              - jboss datasource i.e. testDS1-ds.xml

              - jbpm-human-task-5.1.0.Final.jar and jbpm-bam-5.1.0.Final.jar in the runtime used by the human task

               

              By the time jboss and the human task start all tables will be created.

              • 4. Re: JBPM v5 & MySQL Persistence Problem
                Marco Rietveld Novice

                David,

                 

                This is a half-guess, but have you tried adding the hibernate.connection properties you have in your "org.jbpm.task" persistence-unit to the first persistence-unit ("org.jbpm.persistence.jpa").

                 

                You're using hbm2ddl.auto = create in both units, so Hibernate should create the tables. This means that it's problem with the connection with the database.

                 

                Another possible idea is using the JDBC3 driver for mysql -- but again, that's also just a guess.

                • 5. Re: JBPM v5 & MySQL Persistence Problem
                  David C Newbie

                  Thanks for the suggestions, Chris and Marco.

                   

                  It seems the error is related to the Database connection or configuration.

                   

                  This is the typical error Im getting

                   

                  Aug 26, 2011 12:33:22 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute

                  SEVERE: XAER_RMFAIL: The command cannot be executed when global transaction is in the  ACTIVE state

                   

                  I'm having little success in tracking down a solution from goggle.

                   

                  BTW, I'm using  bitronix for TX

                   

                  import bitronix.tm.TransactionManagerServices;

                  import bitronix.tm.resource.jdbc.PoolingDataSource;

                   

                  UserTransaction ut = (UserTransaction) new InitialContext().lookup("java:comp/UserTransaction");
                  ut.begin();

                   

                  PoolingDataSource ds1 = new PoolingDataSource();

                   

                   

                  ds1 = new PoolingDataSource();
                  ds1.setUniqueName("jdbc/testDS1");
                  ds1.setClassName("com.mysql.jdbc.jdbc2.optional.MysqlXADataSource");

                   

                  ds1.setMaxPoolSize(3);
                  ds1.setAllowLocalTransactions(true);
                  ds1.getDriverProperties().put("user", "test1");
                  ds1.getDriverProperties().put("password", "test1");
                  ds1.getDriverProperties().put("URL","jdbc:mysql://localhost:3306/jbpm5db");

                   

                  ds1.init();
                  • 6. Re: JBPM v5 & MySQL Persistence Problem
                    Marco Rietveld Novice

                    David,

                     

                    As far as I know, you can't start a transaction before initializing the data source: would it be possible to first initialize the data source and then start the transaction? (ut.begin() after ds1.init()).

                    • 7. Re: JBPM v5 & MySQL Persistence Problem
                      David C Newbie

                      Thanks, that fixed the problem I have to put the ut.begin around the functions related with the process. DOH.