The oracle scripts use ALTER TABLE ADD CONSTRAINT to add certain indexes, then ALTER TABLE DROP CONSTRAINT to drop it if it needs to change. This does not work if the database schema has been through an export/import cycle between the CREATE and DROP constraint statements, making upgrades fail:
DbSqlSession - upgrading activiti engine schema from 5.14 to 5.16.1
DbSqlSession - Upgrade needed: 514 -> 515. Looking for schema update resource for component 'engine'
DbSqlSession - performing upgrade on engine with resource org/activiti/db/upgrade/activiti.oracle.upgradestep.514.to.515.engine.sql
DbSqlSession - problem during schema upgrade, statement alter table ACT_RE_PROCDEF
add constraint ACT_UNIQ_PROCDEF
unique (KEY_,VERSION_, TENANT_ID_)
java.sql.SQLSyntaxErrorException: ORA-00955: name is already used by an existing object
1) Actual upgrade code (compresssed for space)
a) From activiti.oracle.upgradestep.59.to.510.engine.sql, creating a unique constraint and index
alter table ACT_RE_PROCDEF add constraint ACT_UNIQ_PROCDEF unique (KEY_,VERSION_);
b) activiti.postgres.upgradestep.514.to.515.engine.sql, dropping and recrating the unique constraint and index
alter table ACT_RE_PROCDEF drop constraint ACT_UNIQ_PROCDEF;
alter table ACT_RE_PROCDEF add constraint ACT_UNIQ_PROCDEF unique (KEY_,VERSION_, TENANT_ID_);
2) Diagnosis
The issue is discussed in Oracle support document 370633.1, where the following can be read under the headline "Cause":
"The problem is that the index is not dependent on the constraint after Import. The index is not dropped automatically when the constraint is dropped. You need to drop the index manually to drop the constraint totally. This is a restriction when using export/import."
What really happens is that ADD CONSTRAINT can either reuse an existing index, or create an implicit index. If it creates an implicit index, DROP CONSTRAINT will also drop the index. This is the scenario assumed by the scripts, and it works in the simple case.
However, when a table with a constraint and an implicit index is first exported and then imported, the import process will in fact first create the table, then an explicit index, and finally a constraint. After being imported, DROP CONSTRAINT no longer drops the index, and the following CREATE CONSTRAINT fails.
3) Solutions
In order for the upgrade to work, the sequence needs to be rewritten to drop the index manually:
alter table ACT_RE_PROCDEF drop constraint ACT_UNIQ_PROCDEF;
drop index ACT_UNIQ_PROCDEF;
alter table ACT_RE_PROCDEF add constraint ACT_UNIQ_PROCDEF unique (KEY_,VERSION_, TENANT_ID_);
The problem with this, of course, is that if the schema has not been through export/import, we trade one error for another (ORA-01418: specified index does not exist).
-> This is a good time for the activiti developers to suggest a suitable solution 🙂
In case there are no immediate ideas, I have a suggestion: Our product contains a database upgrade mechanism somewhat like DbSqlSession.executeSchemaResource(). There, we have utilized a syntax similar to the Oracle hint syntax that indicates that errors are to be ignored. In the example below, the "–+ IGNOREERROR" statement means that any error from the next sql-statement should be ignored
alter table ACT_RE_PROCDEF drop constraint ACT_UNIQ_PROCDEF;
–+ IGNOREERROR
drop index ACT_UNIQ_PROCDEF;
alter table ACT_RE_PROCDEF add constraint ACT_UNIQ_PROCDEF unique (KEY_,VERSION_, TENANT_ID_);
-> Is this an interesting solution?
-> If implemented reasonably, would a pull request be of interest?
