Form properties values recording into Activiti6 problem

[edumitre]

Hi,

Activiti 6 didn't seems to support historization into the DB (table ACT_HI_DETAIL) for long (>2000) string form values as for the variables. Is this a design limitation, a misconfiguration or a bug  ...? 

Thanks for your help !

Following the complete scenario :

When attempting to submit a task form with a huge String value via the API WS http://localhost:8082/activiti-app/api/form/form-data (POST) an error is raised claiming the value to be inserted into the ACT_HI_DETAIL is too big :

INTO ACT_HI_DETAIL (ID_, TYPE_, PROC_INST_ID_, ACT_INST_ID_, EXECUTION_ID_, TASK_ID_, TIME_, NAME_, TEXT_) VALUES    (?, ?, ?, ?, ?, ?,   ?,  ?,  ?)  INTO ACT_HI_DETAIL (ID_, TYPE_, PROC_INST_ID_, ACT_INST_ID_, EXECUTION_ID_, TASK_ID_, TIME_, NAME_, TEXT_) VALUES          (?,   ?,  ?,  ?,  ?,  ?, ?,    ?, ?)  ...

Cause: java.sql.SQLDataException: ORA-01401: inserted value too large for column

The method preparing the form data insertion (org.activiti.engine.impl.persistence.entity.HistoricDetailEntityManagerImpl#copyAndInsertHistoricDetailVariableInstanceUpdateEntity) seems to differ from the one preparing the variable variable insertion (org.activiti.engine.impl.persistence.entity.HistoricDetailEntityManagerImpl#copyAndInsertHistoricDetailVariableInstanceUpdateEntity) precisely on the binary support for the huge values :

public HistoricDetailVariableInstanceUpdateEntity copyAndInsertHistoricDetailVariableInstanceUpdateEntity(VariableInstanceEntity variableInstance) {
  HistoricDetailVariableInstanceUpdateEntity historicVariableUpdate = historicDetailDataManager.createHistoricDetailVariableInstanceUpdate();
  historicVariableUpdate.setProcessInstanceId(variableInstance.getProcessInstanceId());
  historicVariableUpdate.setExecutionId(variableInstance.getExecutionId());
  historicVariableUpdate.setTaskId(variableInstance.getTaskId());
  historicVariableUpdate.setTime(getClock().getCurrentTime());
  historicVariableUpdate.setRevision(variableInstance.getRevision());
  historicVariableUpdate.setName(variableInstance.getName());
  historicVariableUpdate.setVariableType(variableInstance.getType());
  historicVariableUpdate.setTextValue(variableInstance.getTextValue());
  historicVariableUpdate.setTextValue2(variableInstance.getTextValue2());
  historicVariableUpdate.setDoubleValue(variableInstance.getDoubleValue());
  historicVariableUpdate.setLongValue(variableInstance.getLongValue());

  if (variableInstance.getBytes() != null) {
    historicVariableUpdate.setBytes(variableInstance.getBytes());
  }

  insert(historicVariableUpdate);
  return historicVariableUpdate;
}

public HistoricFormPropertyEntity insertHistoricFormPropertyEntity(ExecutionEntity execution, 
    String propertyId, String propertyValue, String taskId) {
  
  HistoricFormPropertyEntity historicFormPropertyEntity = historicDetailDataManager.createHistoricFormProperty();
  historicFormPropertyEntity.setProcessInstanceId(execution.getProcessInstanceId());
  historicFormPropertyEntity.setExecutionId(execution.getId());
  historicFormPropertyEntity.setTaskId(taskId);
  historicFormPropertyEntity.setPropertyId(propertyId);
  historicFormPropertyEntity.setPropertyValue(propertyValue);
  historicFormPropertyEntity.setTime(getClock().getCurrentTime());

  HistoricActivityInstanceEntity historicActivityInstance = getHistoryManager().findActivityInstance(execution, true, false);
  if (historicActivityInstance != null) {
    historicFormPropertyEntity.setActivityInstanceId(historicActivityInstance.getId());
  }
  
  insert(historicFormPropertyEntity);
  return historicFormPropertyEntity;
}

[thuynh]

HI Eugene DUMITRESCU

By default, the column text_ and text2_ in ACT_HI_DETAIL is limited to 4000 bytes

So any value larger than 4000 won't be accepted and therefore you receive the Cause: java.sql.SQLDataException: ORA-01401

What is your use case? 

You said

When attempting to submit a task form with a huge String value

Usually, user won't need to submit forms with such huge String value. Could you share your business use case here? 

Thanks

Thong Huynh

‌

[edumitre]

Hi,

Thanks for your answer !

I agree it is pretty unusual to have such large form values for but we couldn't figure out otherwise : we have processes where human actors interact with "robots" (python scripts which executes some automatic tasks). The huge values are primarily JSON metadata the "robots" needs to communicate between them or JSON data gathered by the robots from miscellaneous sources in order to be transmitted to services called by some other tasks (mostly of them after a human validation).

I am aware of the limitation you've mentioned for the TEXT_ field but I've also noticed that this limitation is bypassed with the usage of BLOBS in the table ACT_GE_BYTEARRAY referenced by the column BYTEARRAY_ID_ of this same table (ACT_HI_DETAIL), used for storage of large historic detail variables instances (see also . org/activiti/db/mapping/entity/HistoricDetail.xml if the "activiti-engine" module)

Best regards, 

Eugene

[thuynh]

Hi Eugene DUMITRESCU ,

The ACT_GE_BYTEARRAY is used to store bytearray variable (serialized Java objects), process definition xml, and images. 

The ACT_HI_DETAIL is used to store historic data such as variables-update, submitted form properties

 Looks like your problem statement is this:

When attempting to submit a task form with a huge String value via the API WS http://localhost:8082/activiti-app/api/form/form-data (POST) an error is raised claiming the value to be inserted into the ACT_HI_DETAIL is too big

Do you have to store this heavy variable in HI (history)? Are you using FormService (Activiti - Engine 5.22.0 API)  method to submit and complete the task? 

Using TaskService.complete(taskId) instead will avoid saving the variable to history tables and therefore avoid this issue. 

(TaskService (Activiti - Engine 5.22.0 API) )

Looks like it's a limitation of the activiti-engine to store large data form property to history tables at the moment. Would you mind create a JIRA issue for this? 

Hope this helps!

Regards,

Thong

‌

[gdharley · Accepted solution]

Eugene,

This is a lonbg standing issue with storing of form variables in the history tables.

The first time I ran across it was version 5.12.

A couple of options:
1. Don't use full or audit history log level

2. Dont save large form data (doesnt really make sense anyway)

3. Overload the history manager to handle long data (a pretty easy overload).

Cheers,

Greg

‌

[edumitre]

Hi Thong Huynh

Thank you for your reply !

The ACT_GE_BYTEARRAY is used to store bytearray variable (serialized Java objects), process definition xml, and images. 

The ACT_HI_DETAIL is used to store historic data such as variables-update, submitted form properties

I've noticed the over-sized text values of variables goes also in this table.

Looks like your problem statement is this: 

When attempting to submit a task form with a huge String value via the API WS http://localhost:8082/activiti-app/api/form/form-data (POST) an error is raised claiming the value to be inserted into the ACT_HI_DETAIL is too big

That is correct.

Do you have to store this heavy variable in HI (history)? Are you using FormService (Activiti - Engine 5.22.0 API)  method to submit and complete the task? 

Yes.

Using TaskService.complete(taskId) instead will avoid saving the variable to history tables and therefore avoid this issue. 

(TaskService (Activiti - Engine 5.22.0 API) )

Thank you, this seems to be a very good idea.

Looks like it's a limitation of the activiti-engine to store large data form property to history tables at the moment. Would you mind create a JIRA issue for this? 

Finally I have fixed on our branch this limitation by imitating the method the Activiti engine is using to persist the serialized variables and the patch seems to work pretty well.

I will create the JIRA soon.

Thanks again for your help !

Best Regards,

Eugene

[gdharley]

Yes, BUT. act_g-bytearray is not used for history data.

See my earlier answer.

Greg

‌

[edumitre]

Hello Greg,

Thanks for your answer.

Unfortunately we need to store the values entered by miscellaneous actors for audit and regulation needs.

This is a long standing issue with storing of form variables in the history tables.

Yeah, we have had this issue with Activiti 5 ant this was one on the main reasons we have to migrate to Activiti 6 which has pretty good support for huge variable values history recording.

Don't use full or audit history log level

This is the first thing I've tried but it had triggers some weird behaviors (form values no longer available between tasks ...), so I give up and I patched the code by imitating the way the activiti-engine stores big variable values.

Overload the history manager to handle long data (a pretty easy overload).

This seems to be cleaner than changing the activiti engine, I will look how one can accomplish that.

Many thanks !

Eugene

[edumitre]

Greg, I agree the table  ACT_GE_BYTEARRAY is intended to be used mainly for binary data storage.

However, it seems to me that the Activiti engine actually uses this table to record at least big text variable values history as we can see in the folloing classes in activiti-engine module: org.activiti.engine.impl.persistence.entity.HistoricDetailEntityManagerImpl#copyAndInsertHistoricDetailVariableInstanceUpdateEntity, org.activiti.engine.impl.persistence.entity.HistoricDetailVariableInstanceUpdateEntity#getByteArrayRef, org.activiti.engine.impl.variable.LongStringType and org.activiti.engine.impl.persistence.entity.ByteArrayRef

Also, both of the ACT_HI_VARINST and ACT_HI_DETAIL tables has a BYTEARRAY_ID_ column which seems to point to this table. 

Best Regards, 

Eugene

[gdharley]

Good point Eugene, I think those references have been in the tables for a long time and dont fit the strategy of separating runtime data from history data.