Modifying TEXT2_ column type in ACT_RU_VARIABLE

[jogahe]

Hi!

First of all, thanks for your attention.

We have an enterprise app that uses Activiti in its version 5.16.1. Actually, we have some problems with this database table:

create table ACT_RU_VARIABLE (
    ID_ NVARCHAR2(64) not null,
    REV_ INTEGER,
    TYPE_ NVARCHAR2(255) not null,
    NAME_ NVARCHAR2(255) not null,
    EXECUTION_ID_ NVARCHAR2(64),
    PROC_INST_ID_ NVARCHAR2(64),
    TASK_ID_ NVARCHAR2(64),
    BYTEARRAY_ID_ NVARCHAR2(64),
    DOUBLE_ NUMBER(*,10),
    LONG_ NUMBER(19,0),
    TEXT_ NVARCHAR2(2000),
    TEXT2_ NVARCHAR2(2000),
    primary key (ID_)
);

In some cases, we try to save objects as JSON strings that are too big and overflow the columns TEXT_ and TEXT2_. Changing them to NVARCHAR2(4000) is not fixing the problem, as in some cases the objects are even bigger.

We tried and changed the datatype of the column TEXT_ to CLOB, causing Activiti to stop working. But if we do the same change to the column TEXT2_ all seems to work perfectly.

My question is … Are we breaking the Activiti engine by doing this? Or can be an acceptable fix?.

Regards.

[faizal-manan]

Hi,

what kind of big object you want to save?
since all variable will be move into *HI* table, you activiti size may grow too fast and you might be facing storage or performance issue later.
or
you may consider to save it to another table.

[jogahe]

Hi!

They are Java beans (many types and fields sizes) saved as JSON strings.

Regards.

[trademak]

Hi,

Why is the overflow behavior an issue for you? It overflows if the JSON string doesn't fix in the one text column.
If the JSON string is bigger than the both text columns combined it will be store as a byte array in the act_ge_bytearray table.

Best regards,

[jogahe]

Hi!

Thanks for your reply. I have seen that in ACT_GE_BYTEARRAY are only stored my *.BPMN (and a .PNG that I generate for each BPMN), but no JSON objects. This is the error that I can see when JSONs are too large:

Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column
### The error may involve org.activiti.engine.impl.persistence.entity.VariableInstanceEntity.insertVariableInstance-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_VARIABLE (ID_, REV_,     TYPE_, NAME_, PROC_INST_ID_, EXECUTION_ID_, TASK_ID_, BYTEARRAY_ID_,     DOUBLE_, LONG_ , TEXT_, TEXT2_)     values (     ?,     1,     ?,     ?,     ?,     ?,     ?,     ?,     ?,     ?,     ?,     ?     )

I don't understand why it is complaining about LONG value … The only thing I know is that changing the TEXT2_ column to CLOB solves the error.

Thanks!