Duplicate SQL insert primary key error in ACT_HI_* tables in workflow

Hi,

I have been using flowable v6.2.0 in a springboot application v1.5.7. Its been more than a year since I integrated flowable engine and it is working like a charm until yesterday I faced some weird issues on production server. After checking the log lines, there were random errors in flowable manged history tables.

ERROR o.f.e.c.i.interceptor.CommandContext.logException - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3381’ for key ‘PRIMARY’
The error may involve org.flowable.identitylink.service.impl.persistence.entity.HistoricIdentityLinkEntityImpl.bulkInsertHistoricIdentityLink-Inline
The error occurred while setting parameters
SQL: insert into ACT_HI_IDENTITYLINK (ID_, TYPE_, USER_ID_, GROUP_ID_, TASK_ID_, PROC_INST_ID_, CREATE_TIME_) values (?, ?, ?, ?, ?, ?, ?) , (?, ?, ?, ?, ?, ?, ?)
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3381’ for key ‘PRIMARY’

20-05-2020 07:05:43.551 [http-nio-8090-exec-24] ERROR o.f.e.c.i.interceptor.CommandContext.logException - Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘2957’ for key ‘PRIMARY’
The error may involve org.flowable.variable.service.impl.persistence.entity.HistoricVariableInstanceEntityImpl.bulkInsertHistoricVariableInstance-Inline
The error occurred while setting parameters
SQL: insert into ACT_HI_VARINST (ID_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, NAME_, REV_, VAR_TYPE_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_, BYTEARRAY_ID_, DOUBLE_, LONG_ , TEXT_, TEXT2_, CREATE_TIME_, LAST_UPDATED_TIME_) values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘2957’ for key ‘PRIMARY’

20-05-2020 08:15:18.736 [http-nio-8090-exec-24] ERROR c.i.z.core.services.RequestService.submitRequest -
Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3422’ for key ‘PRIMARY’
The error may involve org.flowable.engine.impl.persistence.entity.HistoricActivityInstanceEntityImpl.bulkInsertHistoricActivityInstance-Inline
The error occurred while setting parameters
SQL: insert into ACT_HI_ACTINST ( ID_, REV_, PROC_DEF_ID_, PROC_INST_ID_, EXECUTION_ID_, ACT_ID_, TASK_ID_, CALL_PROC_INST_ID_, ACT_NAME_, ACT_TYPE_, ASSIGNEE_, START_TIME_, END_TIME_, DURATION_, DELETE_REASON_, TENANT_ID_ ) values (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) , (?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3422’ for key ‘PRIMARY’

Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3458’ for ke
y ‘PRIMARY’
The error may involve org.flowable.task.service.impl.persistence.entity.HistoricTaskInstanceEntityImpl.insertHistoricTaskInstance-Inline
The error occurred while setting parameters
SQL: insert into ACT_HI_TASKINST ( ID_, REV_, PROC_DEF_ID_, PROC_INST_ID_, EXECUTION_ID_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_, SCOPE_DEFINITION_ID_, NAME_, PARENT_TASK_ID_, DESCRIPTION_, OWNER_, ASSIGNEE_, START_TIME_, CLAIM_TIME_, END_TIME_, DURATION_, DELETE_REASON_, TASK_DEF_KEY_, FORM_KEY_, PRIORITY_, DUE_DATE_, CATEGORY_, TENANT_ID_, LAST_UPDATED_TIME_ ) values ( ?, 1, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )
Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry ‘3458’ for key ‘PRIMARY’

I have no clue what suddenly happened and why it started to throw these duplicate primary key errors on production server. There were lot of workflow tasks failing to complete. I rebooted the server and everything seems to be fine now but I am afraid this can happen anytime in the future.

Please guide and let me know how I could fix this.
Awaiting for a quick response

Thanks

Awaiting for a reply…Please help

Hey @amitshukla,

Are you running multiple instances of the application? Did this happen during a high load of the application?

The version you are using from Flowable is old and uses the DbIdGenerator. In newer version the default IdGenerator when using Spring Boot is the StrongUuidGenerator. I would advise to either upgrade your Flowable version or configure your current one to use the StrongUuidGenerator.

Cheers,
Filip

@filiphr so we have multi-tenant setup with each tenant having separate database. So based on request headers we identify the tenant and then do further transactions corresponding to the tenant. The load was around 5-10% at that moment both on application server and database.

Just quick questions based on your helpful advise

  1. If I do an upgrade of Flowable version, will there be any impact on current workflow tasks which are in process/pending for completion on users/group of users ?
  2. Where can I configure StrongUuidGenerator for workflow managed tables ?

Please guide and thank you so much for the reply.

They will continue running as before with the definitions you have deployed.

I think that in 6.2.0 you’ll need to provide a bean of type ProcessEngineConfigurationConfigurer and configure the engine via it. You can set it on the engine.

In the newest version you only need to provide a bean of type IdGenerator.

Cheers,
Filip

1 Like

Thank you so much @filiphr for a quick response and help. Will try this and let you know.