Missing DB column after migration to Activiti 5.11

[mmaker1234]

Hello Activiti developers,

I just migrated from Activiti 5.10 to 5.11. I started my application once with parameter databaseSchemaUpdate=true  (then turned the parameter value back to "false") therefore the DB schema should be upgraded. No errors were noticed during the upgrade. Currently the DB properties info looks like

schema.version   5.11
schema.history   create(5.7) upgrade(5.7->5.8) upgrade(5.8->5.9) upgrade(5.9->5.9-SNAPSHOT) upgrade(5.9-SNAPSHOT->5.10) upgrade(5.10->5.11-SNAPSHOT) upgrade(5.11-SNAPSHOT->5.11)

"5.x-SNAPSHOT" refers to Activiti source code snapshot downloaded shortly (a week or two) after the release of 5.x

Unfortunately, when I now try to call

      ManagementService service = processEngine.getManagementService();
      
      job = service.createJobQuery().jobId( jobId ).singleResult();
      if(job == null) {
        log("Can not find a job with ID " + jobId);
      } else {
        service.executeJob( job.getId() );
      }‍‍‍‍‍‍‍‍

, I receive the following error:

Dec 18, 2012 11:05:43 AM org.activiti.engine.impl.interceptor.CommandContext close
SEVERE: Error while closing command context
org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: ORA-00904: "PROC_DEF_ID_": invalid identifier

### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.insertMessage-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_JOB (             ID_,              REV_,              TYPE_,             LOCK_OWNER_,              LOCK_EXP_TIME_,             EXCLUSIVE_,             EXECUTION_ID_,              PROCESS_INSTANCE_ID_,             PROC_DEF_ID_,             RETRIES_,              EXCEPTION_STACK_ID_,             EXCEPTION_MSG_,             HANDLER_TYPE_,             HANDLER_CFG_)     values (?,             1,             'message',             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?     )
### Cause: java.sql.SQLException: ORA-00904: "PROC_DEF_ID_": invalid identifier

    at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:147)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:134)
    at org.activiti.engine.impl.db.DbSqlSession.flushInserts(DbSqlSession.java:632)
    at org.activiti.engine.impl.db.DbSqlSession.flush(DbSqlSession.java:459)
    at org.activiti.engine.impl.interceptor.CommandContext.flushSessions(CommandContext.java:167)
    at org.activiti.engine.impl.interceptor.CommandContext.close(CommandContext.java:114)
    at org.activiti.engine.impl.interceptor.CommandContextInterceptor.execute(CommandContextInterceptor.java:69)
    at org.activiti.engine.impl.interceptor.LogInterceptor.execute(LogInterceptor.java:32)
    at org.activiti.engine.impl.ManagementServiceImpl.executeJob(ManagementServiceImpl.java:57)
    at org.activiti.engine.impl.ant.ExecuteJob.execute(ExecuteJob.java:38)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at ise.antelope.tasks.IfTask.doIf(IfTask.java:298)
    at ise.antelope.tasks.IfTask.execute(IfTask.java:272)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:62)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.taskdefs.MacroInstance.execute(MacroInstance.java:394)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.taskdefs.Sequential.execute(Sequential.java:62)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.taskdefs.MacroInstance.execute(MacroInstance.java:394)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at ise.antelope.tasks.Case.execute(Case.java:151)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at ise.antelope.tasks.SwitchTask.execute(SwitchTask.java:127)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor23.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at ise.antelope.tasks.Repeat.repeatTasks(Repeat.java:302)
    at ise.antelope.tasks.Repeat.execute(Repeat.java:265)
    at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
    at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)
    at org.apache.tools.ant.Task.perform(Task.java:348)
    at org.apache.tools.ant.Target.execute(Target.java:357)
    at org.apache.tools.ant.Target.performTasks(Target.java:385)
    at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)
    at org.apache.tools.ant.Project.executeTarget(Project.java:1306)
    at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
    at org.apache.tools.ant.Project.executeTargets(Project.java:1189)
    at org.apache.tools.ant.Main.runBuild(Main.java:758)
    at org.apache.tools.ant.Main.startAnt(Main.java:217)
    at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
    at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)
Caused by: java.sql.SQLException: ORA-00904: "PROC_DEF_ID_": invalid identifier

    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:742)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:212)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:951)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1159)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3284)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3389)
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:41)
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:66)
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:45)
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:108)
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:75)
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:145)
    … 83 more
Job with ID 68322 can not be executed due to the following problem: org.apache.ibatis.exceptions.PersistenceException:
### Error updating database.  Cause: java.sql.SQLException: ORA-00904: "PROC_DEF_ID_": invalid identifier

### The error may involve org.activiti.engine.impl.persistence.entity.JobEntity.insertMessage-Inline
### The error occurred while setting parameters
### SQL: insert into ACT_RU_JOB (             ID_,              REV_,              TYPE_,             LOCK_OWNER_,              LOCK_EXP_TIME_,             EXCLUSIVE_,             EXECUTION_ID_,              PROCESS_INSTANCE_ID_,             PROC_DEF_ID_,             RETRIES_,              EXCEPTION_STACK_ID_,             EXCEPTION_MSG_,             HANDLER_TYPE_,             HANDLER_CFG_)     values (?,             1,             'message',             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?,             ?     )
### Cause: java.sql.SQLException: ORA-00904: "PROC_DEF_ID_": invalid identifier
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Please note that the job was created while version 5.10 was running.

Any ideas what might went wrong and how to fix it?

[jbarrez]

Please note that the job was created while version 5.10 was running.

Shouldn't be an issue, as null is a valid value for that column.

Any ideas what might went wrong and how to fix it?

This seems fishy:

upgrade(5.10->5.11-SNAPSHOT) upgrade(5.11-SNAPSHOT->5.11)

You upgraded from a snapshot version instead of a real version, pretty hard to predict how the database looks like now :-s

You could always manually add the column, copy-pasting from the Activiti upgrade DDL scripts.

[mmaker1234]

Hello Joram,

I preferred to avoid bothering our administrators to run the scripts manually.

It is strange why the DB upgrade procedure does not report any errors to alarm for the problems as early as possible in the development cycle.

Nevertheless, I run the scripts manually and below is an excerpt from the log:

Error starting at line 1 in command:
alter table ACT_RE_PROCDEF
    alter column KEY_ set not null
Error report:
SQL Error: ORA-01735: invalid ALTER TABLE option
01735. 00000 -  "invalid ALTER TABLE option"
*Cause:   
*Action: –This is because 5.11_SNAPSHOT already added this constraint (Oracle are famous for their odd error messages).

…
table ACT_RE_MODEL created.
index ACT_IDX_MODEL_SOURCE created.

Error starting at line 40 in command:
alter table ACT_RE_MODEL
    add constraint ACT_FK_MODEL_SOURCE
    foreign key (EDITOR_SOURCE_VALUE_ID_)
    references ACT_GE_BYTEARRAY (ID_) –Note: The type of ACT_GE_BYTEARRAY ID_ is NVARCHAR2(64 CHAR) while the type of ACT_IDX_MODEL_SOURCE.EDITOR_SOURCE_VALUE_ID_ is VARCHAR2(64 BYTE). These columns are incompatible by both type and size. My research proved that the ACT_GE_BYTEARRAY ID_ field is defined only in activiti.oracle.create.engine.sql and there are no upgrade scripts to alter it, i.e.the problem is not related to 5.11-SNAPSHOT version.
Error report:
SQL Error: ORA-02267: column type incompatible with referenced column type
02267. 00000 -  "column type incompatible with referenced column type"
*Cause:    The datatype of the referencing column is incompatible with the

index ACT_IDX_MODEL_SOURCE_EXTRA created.

Error starting at line 46 in command:
alter table ACT_RE_MODEL
    add constraint ACT_FK_MODEL_SOURCE_EXTRA
    foreign key (EDITOR_SOURCE_EXTRA_VALUE_ID_)
    references ACT_GE_BYTEARRAY (ID_) –See the notes for the error above
Error report:
SQL Error: ORA-02267: column type incompatible with referenced column type
02267. 00000 -  "column type incompatible with referenced column type"
*Cause:    The datatype of the referencing column is incompatible with the

index ACT_IDX_MODEL_DEPLOYMENT created.

Error starting at line 52 in command:
alter table ACT_RE_MODEL
    add constraint ACT_FK_MODEL_DEPLOYMENT
    foreign key (DEPLOYMENT_ID_)
    references ACT_RE_DEPLOYMENT (ID_) –The reason is the same as above, although the tables and columns are different
Error report:
SQL Error: ORA-02267: column type incompatible with referenced column type
02267. 00000 -  "column type incompatible with referenced column type"
*Cause:    The datatype of the referencing column is incompatible with the
…

Now I need to revert the code in the source control system but the problem was found after we upgraded our test server. Now the question is: How I can downgrade the Activiti DB structure while keeping the existing data?

The structural changes in Oracle are not transactional and take place immediately, i.e. it is possible some parts of the scripts to be executed while others - not. How can I find what was actually changed during the last upgrade of the DB structure?

[mmaker1234]

Hello again,

I further reviewed the history DB upgrade script 5.10-to-5.11 for Oracle. Here is what I found:

1. In Oracle the default measurement in definition of char-based fields length is BYTEs. Therefore the fields in the new table ACT_HI_VARINST are of half length ( ID_ NVARCHAR2(64 [BYTE]) ) compared to similar field definitions in ACT_RU_EXECUTION ( ID_ NVARCHAR2(64 CHAR), PROC_INST_ID_ NVARCHAR2(64 CHAR) ), for example. As there are no foreign key constraints (to check at creation time) this difference is a good candidate for a time-bomb;

2. The note above is also valid for the changes in the ACT_HI_ACTINST and ACT_HI_DETAIL tables;

Please note that my observations might be not important at all but these differences are suspicious to me.

3. The names for the indexes on ACT_HI_VARINST are reflecting the name of a different table: ACT_IDX_HI_PROCVAR_PROC_INST, ACT_IDX_HI_PROCVAR_NAME_TYPE

[frederikherema1]

Don't really see a difference in the create-scripts for the tables mentioned, both NVARCHAR2(64) is used, can you clarify why one would be CHAR ant the over BYTE:


create table ACT_RU_EXECUTION (
    ID_ NVARCHAR2(64),
    REV_ INTEGER,
    PROC_INST_ID_ NVARCHAR2(64),
    BUSINESS_KEY_ NVARCHAR2(255),

    …

create table ACT_HI_VARINST (
    ID_ NVARCHAR2(64) not null,
    PROC_INST_ID_ NVARCHAR2(64),
    EXECUTION_ID_ NVARCHAR2(64),

3. The index names are indeed different from the table name, probably it changed name during development.

[mmaker1234]

Sorry for this, it is my mistake (I have no much experience with NVarChar type). It seems that the default measurement for NVarChar(2) is CHAR, while for VarChar(2) is BYTE.

To be honest, I didn't checked the scripts but looked at my tables with SQL Developer. It claimed that ACT_RU_EXECUTION.ID_ is NVARCHAR2(64 CHAR) and my knowledge on VarChar misled me to think that the definition "ID_ NVARCHAR2(64)" will reserve bytes instead of chars.


The other questions, though, are still open:
- Why the upgrade procedure does not report on errors (print a message, at least);
- The different types of ACT_RE_MODEL.EDITOR_SOURCE_VALUE_ID_ and ACT_GE_BYTEARRAY.ID_

Best Regards,
Monique

[frederikherema1]

1. The upgrade (as of 5.12) will throw exception if you try to perform 5.X-SNAPSHOT to 5.X version upgrade, so this won't cause any issues…
2. The type is the same…


create table ACT_GE_BYTEARRAY (
    ID_ NVARCHAR2(64),
    …

create table ACT_RE_MODEL (
    ID_ NVARCHAR2(64) not null,
    …
    DEPLOYMENT_ID_ NVARCHAR2(64),
    EDITOR_SOURCE_VALUE_ID_ NVARCHAR2(64),


[mmaker1234]

You are right - these are the definitions in the scripts.

Table ACT_RE_MODEL is not included in the 5.11_SNAPSHOT version. Then it is unexplainable how the following structure exists into my DB (excerpt from a reverse engineering)   CREATE TABLE "ACTIVITI"."ACT_RE_MODEL"
   ( "ID_" VARCHAR2(64 BYTE) NOT NULL ENABLE,
"REV_" NUMBER(*,0),
"NAME_" VARCHAR2(255 BYTE),
"KEY_" VARCHAR2(255 BYTE),
"CATEGORY_" VARCHAR2(255 BYTE),
"CREATE_TIME_" TIMESTAMP (6),
"LAST_UPDATE_TIME_" TIMESTAMP (6),
"VERSION_" NUMBER(*,0),
"META_INFO_" VARCHAR2(4000 BYTE),
"DEPLOYMENT_ID_" VARCHAR2(64 BYTE),
"EDITOR_SOURCE_VALUE_ID_" VARCHAR2(64 BYTE),
"EDITOR_SOURCE_EXTRA_VALUE_ID_" VARCHAR2(64 BYTE),
  PRIMARY KEY ("ID_")
   )
😞