Cannot reassign task - DB error

neplatnyudaj · June 26, 2019, 12:49pm

I need to start using user tasks in Camunda and encountered some issue with reassigning/reclaiming tasks. The process is simple as this:

I have two users who are administrators so they should have all the rights. When I create a process instance, each of the users is able to claim the task. But when the task is claimed and unclaimed by one user, the other user cannot claim it because of this exception:

ENGINE-03004 Exception while executing Database Operation 'INSERT AuthorizationEntity[769d38b0-980e-11e9-a093-144f8adf0ac5]' with message ' ### Error updating database. Cause: org.h2.jdbc.JdbcSQLException: Unique index or primary key violation: "ACT_UNIQ_AUTH_GROUP_INDEX_6 ON PUBLIC.ACT_RU_AUTHORIZATION(TYPE_, GROUP_ID_, RESOURCE_TYPE_, RESOURCE_ID_) VALUES (1, NULL, 7, 'a8daf824-980d-11e9-a093-144f8adf0ac5', 51)"; SQL statement: insert into ACT_RU_AUTHORIZATION ( ID_, TYPE_, GROUP_ID_, USER_ID_, RESOURCE_TYPE_, RESOURCE_ID_, PERMS_, REV_ ) values ( ?, ?, ?, ?, ?, ?, ?, 1 ) [23505-197] ### The error may involve org.camunda.bpm.engine.impl.persistence.entity.AuthorizationEntity.insertAuthorization-Inline ### The error occurred while setting parameters ### 
SQL: insert into ACT_RU_AUTHORIZATION ( ID_, TYPE_, GROUP_ID_, USER_ID_, RESOURCE_TYPE_, RESOURCE_ID_, PERMS_, REV_ ) values ( ?, ?, ?, ?, ?, ?, ?, 1 ) ### Cause: org.h2.jdbc.JdbcSQLException: 
Unique index or primary key violation: "ACT_UNIQ_AUTH_GROUP_INDEX_6 ON PUBLIC.ACT_RU_AUTHORIZATION(TYPE_, GROUP_ID_, RESOURCE_TYPE_, RESOURCE_ID_) VALUES (1, NULL, 7, 'a8daf824-980d-11e9-a093-144f8adf0ac5', 51)"; 
SQL statement: insert into ACT_RU_AUTHORIZATION ( ID_, TYPE_, GROUP_ID_, USER_ID_, RESOURCE_TYPE_, RESOURCE_ID_, PERMS_, REV_ ) values ( ?, ?, ?, ?, ?, ?, ?, 1 ) [23505-197]'. 
Flush summary: [ INSERT AuthorizationEntity[769d38b0-980e-11e9-a093-144f8adf0ac5] INSERT HistoricIdentityLinkLogEventEntity[769d38b1-980e-11e9-a093-144f8adf0ac5] UPDATE TaskEntity[a8daf824-980d-11e9-a093-144f8adf0ac5] UPDATE HistoricActivityInstanceEventEntity[Task_07vabcb:a8daf823-980d-11e9-a093-144f8adf0ac5] UPDATE HistoricTaskInstanceEventEntity[a8daf824-980d-11e9-a093-144f8adf0ac5] ]

I’ve also tried assign/unassign, but with the same result.
I don’t think this is the same issue as problem reported here: https://app.camunda.com/jira/browse/CAM-9118 because I’m using proper unclaim/unassign calls instead of claiming/assigning to empty user.
This doesn’t look like a permission issue, but as a bug somewhere in the persistence(maybe just in the history part?)

I’m using LDAP authorization plugin, but I guess that shouldn’t be relevant as the task assignments doesn’t perform any checks according to the documentation.

I’m using camunda-bpm-spring-boot-starter-webapp version 3.2.0(based on camunda 7.10)
Update: Tried with latest camunda 7.11 and the issue persists
Update2: It also happens when I set history level to none.
Update3: After checking what’s happening in the database, it seems the issue is that the entry in ACT_RU_AUTHORIZATION is not cleared when doing unclaim/unassign.

Yana · June 26, 2019, 2:16pm

Hi, could you please share your application properties/yaml file

neplatnyudaj · June 26, 2019, 2:18pm

Here’s my application.yaml:

spring.application.name: camundaApplication
spring:
  datasource:
    jdbcBatchProcessing: false    
    url: jdbc:h2:mem:example-simple;MODE=Oracle;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
  h2:
    console:
      enabled: true
camunda:
  bpm:
    database:
      type: h2
      jdbcBatchProcessing: false
    application:
      delete-upon-undeploy: false
      scan-for-process-definitions: false
      deploy-changed-only: true
      resume-previous-versions: true
      resume-previous-by: a value
    job-execution:
      enabled: true
      failed-job-retry-time-cycle: R1/PT5M
      core-pool-size: 20
      max-pool-size: 40
      queue-capacity: 10
    metrics:
      enabled: true
      db-reporter-activate: true    
    history-level: NONE
    default-serialization-format: application/xml
    authorization:
      enabled: true

Yana · June 26, 2019, 2:30pm

Once the task is assigned a new task authorization (read/update) for the assignee is created.
If the task is reassigned the new assignee is granted with read/update permission as well. The old assignee still has the permissions. If the first assignee is assigned again a new authorization is not created. The permissions are deleted when the task is completed.

I was not able to reproduce the issue.
Could you please share the exact steps to reproduce the issue (user A is assigned by user A, user A is unassigned by user B, and so on.)

neplatnyudaj · June 26, 2019, 2:39pm

The scenario is this:

Start new process instance via Rest API
Login with User1 and go to the Tasklist
Find the user task and click “Claim” -> The “Claim” link changes to “User1”
Click the “X”(unclaim) next to the link -> The link changes from “User1” to “Claim” again(but nothing happens in the database.
Login with another user - User2
Find the task in the tasklist -> It appears unassigned(There’s a “Claim” link)
Click on the “Claim” link -> Error(shown above)

If I understand the table ACT_RU_AUTHORIZATION correctly, it stores both user and group authorization. When setting user authorization, group authorization is null, which creates duplicate entry according to ACT_UNIQ_AUTH_GROUP_INDEX_6 constraint. Did you try that with the h2 database? Maybe there’s just some issue in the H2 init scripts.

Edit. Looks like that’s the case.
In the oracle db script(activiti.oracle.create.engine.sql) there’s this:

-- see http://stackoverflow.com/questions/675398/how-can-i-constrain-multiple-columns-to-prevent-duplicates-but-ignore-null-value
create unique index ACT_UNIQ_AUTH_USER on ACT_RU_AUTHORIZATION
(case when USER_ID_ is null then null else TYPE_ end,
 case when USER_ID_ is null then null else RESOURCE_TYPE_ end,
 case when USER_ID_ is null then null else RESOURCE_ID_ end,
 case when USER_ID_ is null then null else USER_ID_ end);

create unique index ACT_UNIQ_AUTH_GROUP on ACT_RU_AUTHORIZATION
(case when GROUP_ID_ is null then null else TYPE_ end,
 case when GROUP_ID_ is null then null else RESOURCE_TYPE_ end,
 case when GROUP_ID_ is null then null else RESOURCE_ID_ end,
 case when GROUP_ID_ is null then null else GROUP_ID_ end);

But in the h2(activiti.h2.create.engine.sql), there’s just this:

alter table ACT_RU_AUTHORIZATION
add constraint ACT_UNIQ_AUTH_USER
unique (TYPE_, USER_ID_,RESOURCE_TYPE_,RESOURCE_ID_);

alter table ACT_RU_AUTHORIZATION
add constraint ACT_UNIQ_AUTH_GROUP
unique (TYPE_, GROUP_ID_,RESOURCE_TYPE_,RESOURCE_ID_);

It will take me some time to verify it works on Oracle, but it looks like this might be the case.

neplatnyudaj · June 27, 2019, 9:00am

It is working in Oracle database. There seems to be a bug in the init scripts for H2 database.

Yana · June 27, 2019, 2:23pm

You mentioned that you use LDAP, I don’t think that the usual scenario is LDAP + h2.
I recommend to stick to some non-h2 db, Oracle is a good choice.

neplatnyudaj · June 27, 2019, 2:38pm

I use Oracle on servers, but for development I use in-memory H2. Anyway from what I’ve found it doesn’t seem to be related to the LDAP at all(I haven’t test it, but I’m pretty sure). It’s just an oversight in the H2 init scripts. It’s probably not a common use case to use user tasks with H2 so noone encountered this problem before it. The workaround is pretty easy… simply dropping the offending constraint after start.

system · January 30, 2024, 11:48am