topic Re: Bug in oracle-DDL's in Alfresco Archive

Bug in oracle-DDL's

hbraun — Mon, 06 Oct 2014 08:58:43 GMT

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 t

Re: Bug in oracle-DDL's

martin_grofcik — Tue, 07 Oct 2014 05:26:57 GMT

Hi Harry,

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

Regards
Martin

Re: Bug in oracle-DDL's

hbraun — Wed, 08 Oct 2014 15:34:10 GMT

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

Re: Bug in oracle-DDL's

martin_grofcik — Thu, 09 Oct 2014 05:17:32 GMT

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

Re: Bug in oracle-DDL's

hbraun — Thu, 09 Oct 2014 09:02:28 GMT

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

Re: Bug in oracle-DDL's

martin_grofcik — Fri, 10 Oct 2014 06:37:32 GMT

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

Re: Bug in oracle-DDL's

trademak — Fri, 10 Oct 2014 07:28:45 GMT

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,

Re: Bug in oracle-DDL's

hbraun — Fri, 10 Oct 2014 09:13:27 GMT

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

Re: Bug in oracle-DDL's

jbarrez — Fri, 17 Oct 2014 10:22:21 GMT

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

Re: Bug in oracle-DDL's

hbraun — Fri, 17 Oct 2014 10:35:54 GMT

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

best regards,

Harry

Re: Bug in oracle-DDL's

jbarrez — Mon, 20 Oct 2014 08:44:01 GMT

Damn auto-assignment of jira 😉

Re: Bug in oracle-DDL's

martin_grofcik — Tue, 21 Oct 2014 07:08:58 GMT

Hi Joram, Harry,
I would say that it is configuration issue. customPreVariableTypes in activiti process engine configuration can override default behavior.


    <util:list id="preVariableTypes">
        <bean class="org.activiti.engine.impl.variable.NullType"/>
        <bean class="org.activiti.engine.impl.variable.StringType">
            <constructor-arg value="2000"/>
        </bean>
        <bean class="org.activiti.engine.impl.variable.LongStringType">
            <constructor-arg value="2001"/>
        </bean>
    </util:list>

Regards
Martin

Re: Bug in oracle-DDL's

hbraun — Tue, 21 Oct 2014 07:51:05 GMT

Hi Martin,

after adding customPreVariableTypes, acitiviti adds the standard types with String(4000) and LongString(4001), see posting #4. AddType override the type in the hashtable, but add a second one to the list. So you can't override a standard type safely.

Best regards,

Harry

Re: Bug in oracle-DDL's

fandji — Wed, 14 Jan 2015 10:31:43 GMT

Hi

I did comment on the Issue: http://jira.codehaus.org/i#browse/ACT-2129 and https://github.com/Activiti/Activiti/pull/401.

Hopefully there will be a good solution found for this problem.

Best regards,

Philippe

Re: Bug in oracle-DDL's

martin_grofcik — Wed, 14 Jan 2015 11:04:41 GMT

Hi Harry,

yes, but custom types are applied first so default types are not used for the Strings.

Regards
Martin

Re: Bug in oracle-DDL's

martin_grofcik — Wed, 14 Jan 2015 11:06:05 GMT

Hi Philippe,

I put my comment there already too.
https://github.com/Activiti/Activiti/pull/401

Regards
Martin

Re: Bug in oracle-DDL's

denisgmarques — Mon, 09 May 2016 19:30:19 GMT

Hi,

We still use Activiti 5.17. Can I modify the Oracle datatype to VARCHAR(4000)? because I have a customer that can't change NATIONAL CHARACTER SET. So the max value to NVARCHAR type in this instance is 2000.

Re: Bug in oracle-DDL's

jbarrez — Tue, 10 May 2016 10:24:48 GMT

Sure, that won't be a probem.