[bug]: All the old instances of diagram [before upgrade] failing at end step [Flows 22.1 to 22.2 Migration Issue]

[SatyaCoder007]

Flows for APEX version

23.1 (latest)

Oracle Database version

21c

Oracle APEX version

23.1

Have you used the BPMN-Linter in Flow Designer to Validate your Diagram?

Yes

What happened?

I migrated the flow for Apex version from 22.2 to 23.1. After the migration, all the old instance of the diagram failing at end step.

Steps to reproduce

Expected behaviour

End step should complete without any error.

[SatyaCoder007]

All the instances which were created after the upgrade, all are working fine and end step is completing successfully. However, resetting the old instances are working fine. In the prod instance we can't reset the flow. We need some solution so that it shouldn't fail at end step.

[rallen2010]

This is a 22.1 to 22.2 migration problem that has surfaced on a double migration from 22.1 -> 22.2 -> 23.1.

In 22.2 we added the call activities feature which required additional columns in flow_subflows to record subflow state regarding its parentage so that subProcesses and callActivities can return to their correct calling object when the subProcess or CallActivity completes.

During the migration from 22.1 to 22.2, we add the column sbfl_calling_sbfl to flow_subflows table in the migration script feature-172.sql. Then set it to ‘0’ for anything that’s not inside a subprocess (so where process_level = 0). Then there is an update for subflows that were in a subprocess to correctly set their sbfl_calling_sbfl. That should have set sbfl_calling_sbfl for all subflows & we should have then added a not null constraint on the column.

It appears that in your case, some subflows were not updated - so the ‘where’ conditions on the 2 updates do not cover all cases.

If you do the update again, it would be interesting to

select count(*) from flow_subflows where sbfl_calling_sbfl is null;

after the update. If this is > 0, there is a problem….

Migration should probably check that all rows are not null, and then add a not null constraint onto flow_subflows.sbfl_calling_sbfl.
Scratch install should also have this not null constraint.