Incorrect datetime value REMOVAL_TIME

Hi guys…

After migrating from 7.9 to 7.11, I started having problems finishing processes due the following error:

### Error flushing statements.  Cause: org.apache.ibatis.executor.BatchExecutorException:
org.camunda.bpm.engine.impl.persistence.entity.HistoricActivityInstanceEntity.updateHistoricActivityInstancesByRootProcessInstanceId (batch index #7) failed. 6 prior sub executor(s) completed successfully, but will be rolled back. 
Cause: java.sql.BatchUpdateException: Data truncation: 
Incorrect datetime value: '5881630-06-06 17:57:19.537' for column 'REMOVAL_TIME_' at row 1

Notice the quite strange year of the timestamp it’s setting for removal_time_.

Any Idea of what could be happening ? :frowning:

PS.: mysql database, java 11

@fcordova how did you upgrade the camunda?

To upgrade camunda version 7.9 to 7.11,

  • Execute patch update db script 7.9.x to 7.9.y
  • Execute minor version update db script 7.10
  • Execute patch update db script 7.10.x to 7.10.y
  • Execute minor version update db script 7.11
1 Like

Do I have to execute path updates for each version before migrating between minor versions? I really expected all patches were included in minor version’s script…

But, considering REMOVAL_TIME_ fields were create on 7.9 to 7.10 script and 7.10’s only patch doesn’t mention REMOVAL_TIME, do you think it’s somehow related to the error I reported?

@fcordova patch script won’t be available in minor update script. It’s incremental approach. You have to execute patch update before the minor version update

OK, I understand…

But I still can’t see how this could be related to the error I reported.
As I said, REMOVAL_TIME_ fields were created in 7.9_to_7.10 and haven’t changed in any patch ever since.

As far as I see, the engine is somehow trying to insert an invalid timestamp, isn’t it?

@fcordova, did you provide custom implementation for HistoryRemovalTimeProvider
in the process engine config by setting this property setHistoryRemovalTimeProvider(HistoryRemovalTimeProvider removalTimeProvider) ?

From the class below function is called, so you can debug this function and caller.

public void addRemovalTimeToActivityInstancesByRootProcessInstanceId(String rootProcessInstanceId, Date removalTime) {
    Map<String, Object> parameters = new HashMap<>();
    parameters.put("rootProcessInstanceId", rootProcessInstanceId);
    parameters.put("removalTime", removalTime);
      .updatePreserveOrder(HistoricActivityInstanceEventEntity.class, "updateHistoricActivityInstancesByRootProcessInstanceId", parameters);

The DB Query is referenced from here for the above function,

 <update id="updateHistoricActivityInstancesByRootProcessInstanceId"
    update ${prefix}ACT_HI_ACTINST set
    REMOVAL_TIME_ = #{removalTime, jdbcType=TIMESTAMP}

    where ROOT_PROC_INST_ID_ = #{rootProcessInstanceId, jdbcType=VARCHAR}

Call Hierarchy for above function is:

Not a custom provider but I have:

  1. changed cleanup strategy with cfg.setHistoryCleanupStrategy("endTimeBased");
  2. changed HistoryTimeToLive to Integer.MAX_VALUE

Looking now, I guess this error could be caused by item 2 since maxvalue is too big… the idea here was to keep it forever, what could be achieved using 0 instead, right?

@fcordova We can configure either of the two history cleanup strategies:

Two avialable history cleanup strategies: Removal-Time-based or End-Time-based strategy. The Removal-Time-based strategy is the default strategy and recommended in most scenarios.

Since your History TTL is too high value, removal time has been set to maximum years. Also it doesn’t match the ISO date format yyyy-MM-ddTHH:mm:sssZ

I believe that setting Integer.MAX_VALUE to HistoryTimeToLive is not a good practice and not a valid use case.

Configure reasonable History TTL according to docs:

  • If you want to retain the history forever for certain process definitions, then remove the TTL settings at engine level and History TTL field in bpmn model also keep it blank.

  • Configure History TTL at process definition level whose history only need to be removed after certain period of time.