deleteSequentiallyUsingBatch not working with HistoricProcessInstanceQuery after upgrading flowable version

Flowable Engine
1

I have upgraded my flowable version to 6.8.0 from 6.6.0. I am trying to write a script that would delete historic data.
I have created HistoricProcessInstanceQuery as -

HistoricProcessInstanceQuery query = processEngineConfiguration.getHistoryService()
                    .createHistoricProcessInstanceQuery()
                    .startedBefore(date);
query.deleteSequentiallyUsingBatch(2, "historic-data-deletion")

But this is not working. HistoricProcessInstanceQuery is successfully getting created and I am getting intended data as well but deleteSequentiallyUsingBatch function is not working. Although query.deleteWithRelatedData() is working perfectly fine.

I have upgraded the flowable version but not the DB version. Can somebody please help me with this?
Also i would like to mention while upgrading flowable from 6.6.0 to 6.8.0 the flowable properties that are set in my spring boot application is-

flowable.idm.enabled=false
flowable.database-schema-update=false
flowable.check-process-definitions=true

Please help me with this issue

Further debugging the issue, and checking the sql history I see that few insert queries are getting triggered internally and delete query is never triggered.

select count(distinct RES.ID_)
       
    from ACT_HI_PROCINST RES
    
    left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_
     
     WHERE  RES.START_TIME_ <= '2023-07-12 00:00:00';

insert into ACT_GE_BYTEARRAY(ID_, REV_, NAME_, BYTES_, DEPLOYMENT_ID_)
    values (
      'd6f9a5a1-3681-11ee-a880-aaaf421bc944',
      1, 
      'batchDocumentJson', 
      _binary'{"numberOfInstances":1,"batchSize":1,"sequential":true,"query":{"startedBefore":"2023-07-11T18:30:00.000Z"}}', 
      null
    );
    
insert into ACT_RU_JOB (
        ID_,
        REV_,
        CATEGORY_,
        TYPE_,
        LOCK_OWNER_,
        LOCK_EXP_TIME_,
        EXCLUSIVE_,
        EXECUTION_ID_,
        PROCESS_INSTANCE_ID_,
        PROC_DEF_ID_,
        ELEMENT_ID_,
        ELEMENT_NAME_,
        SCOPE_ID_, 
        SUB_SCOPE_ID_, 
        SCOPE_TYPE_, 
        SCOPE_DEFINITION_ID_,
        CORRELATION_ID_,
        RETRIES_,
        EXCEPTION_STACK_ID_,
        EXCEPTION_MSG_,
        DUEDATE_,
        REPEAT_,
        HANDLER_TYPE_,
        HANDLER_CFG_,
        CUSTOM_VALUES_ID_,
        CREATE_TIME_,
        TENANT_ID_)
        values ('d6f9ccb5-3681-11ee-a880-aaaf421bc944',
        1,
        null,
        'message',
        null,
        null,
        0,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        'd6f9ccb4-3681-11ee-a880-aaaf421bc944',
        3,
        null,
        null,
        null,
        null,
        'delete-historic-processes-sequential',
        'd6f9a5a3-3681-11ee-a880-aaaf421bc944',
        null,
        '2023-08-09 12:26:18.044',
        ''
        )
        
INSERT INTO FLW_RU_BATCH(ID_, REV_, TYPE_, SEARCH_KEY_, SEARCH_KEY2_, CREATE_TIME_, COMPLETE_TIME_, STATUS_, BATCH_DOC_ID_, TENANT_ID_)
        VALUES ('d6f9a5a2-3681-11ee-a880-aaaf421bc944',
                1,
                'historicProcessDelete',
                'historic-data-deletion',
                null,
                '2023-08-09 12:26:18.043',
                null,
                'inProgress',
                'd6f9a5a1-3681-11ee-a880-aaaf421bc944',
                null)
                
INSERT INTO FLW_RU_BATCH_PART(ID_, REV_, BATCH_ID_, TYPE_, SCOPE_ID_, SUB_SCOPE_ID_, SCOPE_TYPE_, SEARCH_KEY_, SEARCH_KEY2_, STATUS_, CREATE_TIME_, COMPLETE_TIME_, RESULT_DOC_ID_, TENANT_ID_)
        VALUES ('d6f9a5a3-3681-11ee-a880-aaaf421bc944',
            1,
            'd6f9a5a2-3681-11ee-a880-aaaf421bc944',
            'deleteProcess',
            null,
            null,
            null,
            '0',
            null,
            'waiting',
            '2023-08-09 12:26:18.043',
            null,
            null,
            null)

My guess is internally only half of the queries are getting triggered. Any suggestions on why that might be happening?

2

We are deleting in following way

	HistoricProcessInstanceQuery query = Flowable.getConfig().getHistoryCleaningManager().createHistoricProcessInstanceCleaningQuery();
		query.finishedBefore(date);
		query.deleteSequentiallyUsingBatch(BATCH_SIZE, BATCH_NAME);
3

@MirekSz you are using version 6.8.0 right? Also I am not getting function getConfig from Flowable class. What can be the reason for this? Do you think it can be any issue related to the way flowable is configured for us?

Screenshot 2023-08-29 at 2.14.23 PM
Screenshot 2023-08-29 at 2.14.23 PM2198×262 55.5 KB

4

I’m using 6.8.0, Flowable.getConfig() is our wrapper for StandaloneProcessEngineConfiguration

And we have following configuraion

			cfg.setAsyncExecutorActivate(true);
			cfg.setAsyncExecutorMaxPoolSize(16);
			cfg.setAsyncExecutorCorePoolSize(16);
			cfg.setAsyncHistoryExecutorCorePoolSize(16);
			cfg.setAsyncHistoryExecutorMaxPoolSize(16);

After execution of deleteSequentiallyUsingBatch do you see any records in tables flw_ru_batch/flw_ru_batch_part?

5

Yes I see the data both in flw_ru_batch/flw_ru_batch_part tables. I am attaching the entries as well. But it did not delete the process instance

Screenshot 2023-08-29 at 5.32.21 PM
Screenshot 2023-08-29 at 5.32.21 PM2436×206 12.8 KB

Screenshot 2023-08-29 at 5.32.44 PM
Screenshot 2023-08-29 at 5.32.44 PM2582×118 12.6 KB

It do contain the data bit I see the status is InProgress and waiting in flw_ru_batch and flw_ru_batch_part tables respectively. That’s where I am confused that why is it happening. In my detailed description as well I have mentioned that half of the queries are getting executed. @MirekSz also can you help me with your code snippet for wrapper for StandaloneProcessEngineConfiguration. Not sure if that is the problem but can try it once

6

You should wait until records in above tables get status “completed”

`StandaloneProcessEngineConfiguration cfg = new StandaloneProcessEngineConfiguration()

cfg.setAsyncExecutorActivate(true);
cfg.setAsyncExecutorMaxPoolSize(16);
cfg.setAsyncExecutorCorePoolSize(16);
cfg.setAsyncHistoryExecutorCorePoolSize(16);
cfg.setAsyncHistoryExecutorMaxPoolSize(16);

`

7

@MirekSz I am not doing anything with the flw_ru_batch and flw_ru_batch_part tables records. For example I executed the method yesterday and records got created. But till today the records status is InProgress only. Also on the application console i see warnings like-

2023-08-30 10:10:41.021 WARN (HikariPool-1 housekeeper)  [com.zaxxer.hikari.pool.HikariPool] HikariPool-1 - Thread starvation or clock leap detected (housekeeper delta=5m12s869ms).
2023-08-30 10:10:48.307 WARN (HikariPool-2 housekeeper)  [com.zaxxer.hikari.pool.HikariPool] HikariPool-2 - Thread starvation or clock leap detected (housekeeper delta=5m12s870ms).

Also how long do we need to wait in order to see whether the job would complete or not. It’s already 17 hours till the time records got inserted.