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

amitshukla · May 21, 2020, 5:50am

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

amitshukla · May 25, 2020, 11:56am

Awaiting for a reply…Please help

filiphr · May 25, 2020, 7:52pm

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

amitshukla · May 26, 2020, 5:02am

@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

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 ?
Where can I configure StrongUuidGenerator for workflow managed tables ?

Please guide and thank you so much for the reply.

filiphr · May 26, 2020, 6:52am

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

amitshukla · May 27, 2020, 5:49am

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

amitshukla · October 5, 2020, 11:29am

@filiphr I configured StrongUuidGenerator and dint get the same error until today. So again the same error appeared in application log file and it was random some users were facing this error while taking an action while some dint face any issue.

This is how I have configured it creating a class file

package com.inventiahealthcare.zygote.configs;

import org.flowable.engine.common.impl.persistence.StrongUuidGenerator;
import org.flowable.spring.SpringProcessEngineConfiguration;
import org.flowable.spring.boot.ProcessEngineConfigurationConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class FlowableConfigurationConfiguration {
@Bean
public FlowableConfigurationConfiguration flowableConfiguration() {
    return new FlowableConfigurationConfiguration();
}
}
class FlowableConfiguration implements ProcessEngineConfigurationConfigurer {
@Override
public void configure(SpringProcessEngineConfiguration config) {
    config.setIdGenerator(new StrongUuidGenerator());
}
}

Please let me know how I can fix this once and for all as the user’s are suffering once a week and have to rerun the java service and everything works fine then after.

amitshukla · October 7, 2020, 4:47am

Waiting for your reply as how should I configure it correctly so that this issue never appears again. Also even if I upgrade to latest flowable version what difference it would make as even the latest version is using strongUuidGenerator method and I already implemented that but failed.

filiphr · October 7, 2020, 6:26am

Can you please share the logs from the new error?

amitshukla · October 12, 2020, 6:49am

@filiphr PFA the screenshots & log lines of the log if that helps.

29-09-2020 05:31:30.540 [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 '10065' for key '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 '10065' for key 'PRIMARY'
        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
        at org.flowable.engine.common.impl.db.DbSqlSession.flushRegularInsert(DbSqlSession.java:438)
        at org.flowable.engine.common.impl.db.DbSqlSession.flushInsertEntities(DbSqlSession.java:419)
        at org.flowable.engine.common.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:402)
        at org.flowable.engine.common.impl.db.DbSqlSession.flush(DbSqlSession.java:291)
        at org.flowable.engine.common.impl.interceptor.CommandContext.flushSessions(CommandContext.java:193)
        at org.flowable.engine.common.impl.interceptor.CommandContext.close(CommandContext.java:63)
        at org.flowable.engine.common.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:81)
        at org.flowable.spring.SpringTransactionInterceptor$1.doInTransaction(SpringTransactionInterceptor.java:49)
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133)
        at org.flowable.spring.SpringTransactionInterceptor.execute(SpringTransactionInterceptor.java:46)
        at org.flowable.engine.common.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:30)
        at org.flowable.engine.common.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:56)
        at org.flowable.engine.common.impl.cfg.CommandExecutorImpl.execute(CommandExecutorImpl.java:51)
        at org.flowable.engine.impl.TaskServiceImpl.complete(TaskServiceImpl.java:215)
        at com.inventiahealthcare.zygote.utils.ActivityManager.completeTask(ActivityManager.java:106)
        at com.inventiahealthcare.zygote.core.services.ImpactedUnitService.submitAssessment(ImpactedUnitService.java:299)
        at com.inventiahealthcare.zygote.core.services.ImpactedUnitService$$FastClassBySpringCGLIB$$140ed117.invoke(<generated>)

Also Please find the configuration of the main class the way I have configured

@SpringBootApplication(exclude = {org.flowable.spring.boot.SecurityAutoConfiguration.class, 
		DataSourceAutoConfiguration.class, 
		DataSourceTransactionManagerAutoConfiguration.class})
@EnableResourceServer
@EnableScheduling
@EnableTransactionManagement
public class ZygoteApplication {
           ...
}

Past 2 weeks I have not encountered similar issue of duplicate entry in workflow history table and everything is working just fine. When ever this issue occurs, I have to restart the application service and then everything is back to normal.

filiphr · October 12, 2020, 7:18am

Hey @amitshukla,

This indicates that you are not using the StrongUuidIdGenerator. You should verify in your setup that the id generator is correctly set and not changed later by something else.

Cheers,
Filip

amitshukla · October 12, 2020, 7:59am

@filiphr this is how I have configured

package com.inventiahealthcare.zygote.configs;

import org.flowable.engine.common.impl.persistence.StrongUuidGenerator;
import org.flowable.spring.SpringProcessEngineConfiguration;
import org.flowable.spring.boot.ProcessEngineConfigurationConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class FlowableConfigurationConfiguration {

    @Bean
    public FlowableConfigurationConfiguration flowableConfiguration() {
        return new FlowableConfigurationConfiguration();
    }
}

class FlowableConfiguration implements ProcessEngineConfigurationConfigurer {

    @Override
    public void configure(SpringProcessEngineConfiguration config) {
        config.setIdGenerator(new StrongUuidGenerator());
    }
}

Is this not the right way to configure ?
How can I verify this?
Is there any project for reference ?

Topic		Replies	Views
A weird crash when attempt to complete a task Flowable Engine	5	2006	June 8, 2017
Multi instantiation vs business process auto-deployment with Spring Boot Flowable Engine	2	1333	April 25, 2018
Strange question - startProcessInstanceById() cause error Flowable Engine	10	796	November 16, 2020
Flowable Error while trying to persist workflow state to database: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended Flowable Engine	1	614	March 14, 2020
Table 'ACT_GE_PROPERTY' already exists Issue with Springboot and MySQL Flowable Engine	0	44	September 27, 2024

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

Related topics