Bug in oracle-DDL's

[hbraun]

Hi activiti-team,

we had a problem to store string variables with length greater than 2000 chars. An exception  occurs only on oracle databases, with MySQL, H2, etc. it works as expected.
After a while, we found the reason: All DDL define varchars with 4000 chars, only the oracle DDL Scripts create textfields with length 2000.
Most times, it works fine. Only on oracle databases with a string variable with length between 2000 and 4000 you will get an exception. Less than 2000 chars, the string fits in database field, greater then 4000 chars the string is stored as a byte array.

We found the bug in activiti 5.16.1, the DDL in 5.16.3 looks still the same. In our system, we replaced all varchar(2000) with varchar(4000) and it looks fine until now.

Best regards,

Harry Braun

[martin_grofcik]

Hi Harry,

I had the similar problem -> https://github.com/Activiti/Activiti/pull/401

Regards
Martin

[hbraun]

Hi Martin,

I tried the database patch, but oracle returned an error message for NVARCHAR2(4000):
ORA-00910: specified length too long for its datatype

So, there is still a problem using the oracle database with activiti. We can't exclude strings with 2000-4000 chars. I also tried to override StringType and LongStringType with customPreVariableTypes, but the method only add a type, not replace the type.

Regards,

Harry

[martin_grofcik]

Hi Harry,

That's strange. I have tested it with oracle database and jUnit tests. (and tests passed) I would prefer DB patch instead of configuration changes.

If you want to set LongStringType length to 2000, change it in customPreVariableTypes in process engine configuration.

  protected void initVariableTypes() {
    if (variableTypes==null) {
      variableTypes = new DefaultVariableTypes();
      if (customPreVariableTypes!=null) {
        for (VariableType customVariableType: customPreVariableTypes) {
          variableTypes.addType(customVariableType);
        }
      }
      variableTypes.addType(new NullType());
      variableTypes.addType(new StringType(4000));
      variableTypes.addType(new LongStringType(4001));
…

Regards
Martin

[hbraun]

Hi Martin,

I know the code snippet, but we use activiti as a component with different databases, patching activiti isn't the preferred way. Patching the oracle scripts would be perfect, but oracle 11.2 rejects our DDL. I'm not an oracle expert, but I think NVARCHAR depends on the codepage. Perhaps varchar(4000) will fix it, but if there're side effects?
In my opinion, this is a critical bug in activiti. Using activiti 5.16.1 with oracle in production environment is dangerous. If you're lucky, all variables are smaller than 2000 or greater than 4000 chars. The day will come and somebody want to store a variable with 2001 chars, the system throws an exception and your well tested workflow break
Btw. there are other fields with length 2000 in oracle and 4000 in other databases, perhaps the variables are the first visible side effect.

Regards,

Harry

[martin_grofcik]

Hi Harry,

I can only agree with you.
Another possibility is to lower default string size limit to 2000 for all DBs and change DDLs to 2000.
(the side effect is that large strings are not taken into account for queries (variableValue….))

Regards
Martin

[trademak]

Hi Harry,

I don't agree that this is an critical bug. If Oracle is used in production it should also be used in your testing to my opinion.
The reason for the 2000 limit is the following:

"So an NVARCHAR2( 2000 ) with an AL16UTF16 national character set would occupy the maximum 4000 bytes and allow you to encode 2000 Unicode characters."

We might need to change the LongStringType logic in the Engine, so that we switch to LongStringType for Oracle already above 2000 characters, instead of above 4000, which is the logic right now. We'll investigate this further and will implement a fix.

Best regards,

[hbraun]

Hi Tijs,

we stopped using activiti with oracle db and stopped any activiti updates until the bug is fixed, in our case this issue is critical. Switching the database is not an option in financial sector, switching the BPMN-Engine is my next project. (But the target engine is activiti 🙂 )

If LongStringType uses 2000 characters for oracle it would be perfect.

Should I open a jira issue?

Best regards,

Harry

[jbarrez]

Yes, please. or a pull request would even be better 😉

[hbraun]

Well, you're already assigend to http://jira.codehaus.org/browse/ACT-2129 🙂

best regards,

Harry