5 Replies Latest reply on May 22, 2012 3:49 AM by hadik

    Problem Running Task Service in MySQL

    devilkazuya99

      In DemoTaskService.java

              EntityManagerFactory emf = Persistence.createEntityManagerFactory("org.jbpm.task");
              TaskService taskService = new TaskService(emf, SystemEventListenerFactory.getSystemEventListener());
              TaskServiceSession taskSession = taskService.createSession();
      ...
              MinaTaskServer server = new MinaTaskServer(taskService);
              Thread thread = new Thread(server);
              thread.start();
      ... 

      I changed the persistence.xml from using H2 to MySQL like

      ... 

          <properties>

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

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

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

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

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

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

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

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

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

          </properties>

        ...

       

      And I always get this error:

      Cannot add or update a child row: a foreign key constraint fails (`mina_task_server_db`.`task`, CONSTRAINT `FK27A9A59E619A0` FOREIGN KEY (`createdBy_id`) REFERENCES `OrganizationalEntity` (`id`))

       

      Somehow it's the schema constraint issue. And way, I am also interesting to find out about this:

      When I run the service in H2, everything is fine. Then I look at the mydb.trace.db file generated by H2.

      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "ATTACHMENT" not found; SQL statement:
      alter table Attachment drop constraint FK1C935438EF5F064 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "ATTACHMENT" not found; SQL statement:
      alter table Attachment drop constraint FK1C93543F21826D9 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "BOOLEANEXPRESSION" not found; SQL statement:
      alter table BooleanExpression drop constraint FKE3D208C0AFB75F7D [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "DEADLINE" not found; SQL statement:
      alter table Deadline drop constraint FK21DF3E78684BACA3 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "DEADLINE" not found; SQL statement:
      alter table Deadline drop constraint FK21DF3E7827ABEB8A [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "DELEGATION_DELEGATES" not found; SQL statement:
      alter table Delegation_delegates drop constraint FK47485D572C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "DELEGATION_DELEGATES" not found; SQL statement:
      alter table Delegation_delegates drop constraint FK47485D5736B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "ESCALATION" not found; SQL statement:
      alter table Escalation drop constraint FK67B2C6B5C7A04C70 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B2162DFB4 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686BD488CEEB [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B5EEBB6D9 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B3330F6D9 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B8046A239 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B69B21EE8 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686BB2FA6B18 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686B98B62B [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "I18NTEXT" not found; SQL statement:
      alter table I18NText drop constraint FK2349686BF952CEE4 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION" not found; SQL statement:
      alter table Notification drop constraint FK2D45DD0B3E0890B [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_BAS" not found; SQL statement:
      alter table Notification_BAs drop constraint FK2DD68EE02C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_BAS" not found; SQL statement:
      alter table Notification_BAs drop constraint FK2DD68EE09C76EABA [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_RECIPIENTS" not found; SQL statement:
      alter table Notification_Recipients drop constraint FK98FD214E2C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_RECIPIENTS" not found; SQL statement:
      alter table Notification_Recipients drop constraint FK98FD214E9C76EABA [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_EMAIL_HEADER" not found; SQL statement:
      alter table Notification_email_header drop constraint FKF30FE3441F7B912A [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "NOTIFICATION_EMAIL_HEADER" not found; SQL statement:
      alter table Notification_email_header drop constraint FKF30FE34430BE501C [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_BAS" not found; SQL statement:
      alter table PeopleAssignments_BAs drop constraint FK9D8CF4EC2C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_BAS" not found; SQL statement:
      alter table PeopleAssignments_BAs drop constraint FK9D8CF4EC36B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_EXCLOWNERS" not found; SQL statement:
      alter table PeopleAssignments_ExclOwners drop constraint FKC77B97E42C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_EXCLOWNERS" not found; SQL statement:
      alter table PeopleAssignments_ExclOwners drop constraint FKC77B97E436B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_POTOWNERS" not found; SQL statement:
      alter table PeopleAssignments_PotOwners drop constraint FK1EE418D2C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_POTOWNERS" not found; SQL statement:
      alter table PeopleAssignments_PotOwners drop constraint FK1EE418D36B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_RECIPIENTS" not found; SQL statement:
      alter table PeopleAssignments_Recipients drop constraint FKC6F615C22C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_RECIPIENTS" not found; SQL statement:
      alter table PeopleAssignments_Recipients drop constraint FKC6F615C236B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_STAKEHOLDERS" not found; SQL statement:
      alter table PeopleAssignments_Stakeholders drop constraint FK482F79D52C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "PEOPLEASSIGNMENTS_STAKEHOLDERS" not found; SQL statement:
      alter table PeopleAssignments_Stakeholders drop constraint FK482F79D536B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "REASSIGNMENT" not found; SQL statement:
      alter table Reassignment drop constraint FK724D0560A5C17EE0 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "REASSIGNMENT_POTENTIALOWNERS" not found; SQL statement:
      alter table Reassignment_potentialOwners drop constraint FK90B59CFF2C122ED2 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "REASSIGNMENT_POTENTIALOWNERS" not found; SQL statement:
      alter table Reassignment_potentialOwners drop constraint FK90B59CFFE17E130F [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "SUBTASKSSTRATEGY" not found; SQL statement:
      alter table SubTasksStrategy drop constraint FKDE5DF2E136B2F154 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "TASK" not found; SQL statement:
      alter table Task drop constraint FK27A9A59E619A0 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "TASK" not found; SQL statement:
      alter table Task drop constraint FK27A9A56CE1EF3A [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "TASK" not found; SQL statement:
      alter table Task drop constraint FK27A9A5F213F8B5 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "TASK_COMMENT" not found; SQL statement:
      alter table task_comment drop constraint FK61F475A5B35E68F5 [42102-124]
      04-12 14:03:34 jdbc[2]: SQLException
      org.h2.jdbc.JdbcSQLException: Table "TASK_COMMENT" not found; SQL statement:
      alter table task_comment drop constraint FK61F475A52FF04688 [42102-124]
      

       

      So, wehn running in H2, all constraint was dropped. When running in MySQL, the constraint is still there. I tried manually removed the constraints in MySQL and it works, but that is strange for me. Can someone explain what is going on, why H2 drop the constraint and MySQL don't, and is it any thing that I did wrong?

       

      Thank you.

        • 1. Re: Problem Running Task Service in MySQL
          a_nat

          Hi,

          The problem  is in your persistence.xml.

           

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

           

          Change this property to have a valuse like update / create and run it again.

           

          Regards,

          Aparna

          • 2. Re: Problem Running Task Service in MySQL
            devilkazuya99

            Nah. That is not the problem. I started with create. Same thing.

            • 3. Re: Problem Running Task Service in MySQL
              hadik

              If you are using 5.2.0.Final you need to set

               

              System.setProperty("jbpm.usergroup.callback", "org.jbpm.task.service.DefaultUserGroupCallbackImpl");
              

               

              otherwise you need to insert the users and groups into TaskServiceSession which you are using into process.

              • 4. Re: Problem Running Task Service in MySQL
                devilkazuya99

                I added the properties, inserted the users and groups. doesn't help. I turn on the 'hibernate.show_sql', see below.

                 

                ...

                INFO 15:21:59,819 (UserGroupCallbackManager.java:34) - UserGroupCallback registered from system property: org.jbpm.task.service.DefaultUserGroupCallbackImpl

                Hibernate: select user_.id from OrganizationalEntity user_ where user_.id=?

                Hibernate: insert into Task (allowedToDelegate, taskInitiator_id, priority, activationTime, actualOwner_id, createdBy_id, createdOn, documentAccessType, documentContentId, documentType, expirationTime, faultAccessType, faultContentId, faultName, faultType, outputAccessType, outputContentId, outputType, parentId, previousStatus, processId, processInstanceId, processSessionId, skipable, status, workItemId) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

                Hibernate: insert into I18NText (language, text) values (?, ?)

                Hibernate: insert into I18NText (language, text) values (?, ?)

                Hibernate: insert into I18NText (language, text) values (?, ?)

                Hibernate: insert into Content (content) values (?)

                Hibernate: update Task set allowedToDelegate=?, taskInitiator_id=?, priority=?, activationTime=?, actualOwner_id=?, createdBy_id=?, createdOn=?, documentAccessType=?, documentContentId=?, documentType=?, expirationTime=?, faultAccessType=?, faultContentId=?, faultName=?, faultType=?, outputAccessType=?, outputContentId=?, outputType=?, parentId=?, previousStatus=?, processId=?, processInstanceId=?, processSessionId=?, skipable=?, status=?, workItemId=? where id=?

                Hibernate: update I18NText set Task_Descriptions_Id=? where id=?

                WARN 15:15:44,558 (JDBCExceptionReporter.java:233) - SQL Error: 1452, SQLState: 23000

                ERROR 15:15:44,558 (JDBCExceptionReporter.java:234) - Cannot add or update a child row: a foreign key constraint fails (`mina_task_server_test_db`.`i18ntext`, CONSTRAINT `FK2349686B69B21EE8` FOREIGN KEY (`Task_Descriptions_Id`) REFERENCES `Task` (`id`))

                • 5. Re: Problem Running Task Service in MySQL
                  hadik

                  Try to insert the users manually (in taskSession) whom you are assigning the task.

                  Also add 'Administrator' user.

                   

                   

                  Regards,

                  Hardik Dave