Affects Version/s: 4.5.1, 4.4.4, 4.5.2, 4.6.0, 4.6.1, 4.6.2, 4.7.0, 4.7.1, 4.8.0, 4.9.0
Fix Version/s: None
Security Level: Public (Anyone can view this level - this is the default.)
The algorithm for the DB upgrade process in DatabaseUpgradeChecker is incorrect since it can lead to different final state depending on the base version you are running it. The algo runs all schema-xxx-to-xxx.sql files, and then schema-xxx-to-xxx-cleanup.sql.
For example if you have a change in a view in the file schema-460-to-461.sql and another change prior to that version in the file schema-451-to-452-cleanup.sql for the same view.
If you upgrade from 4.5.0 to 4.7.0 directly (or any higher version), the view will be defined as in the schema-451-to-452-cleanup.sql file (which is incorrect).
If you did all Cloudstack upgrade, version after version (4.5.0 -> 4.5.1, 4.5.1 -> 4.5.2, ...), you will have the view definied as in schema-460-to-461.sql, which is the expected result.
The correct algorithm should run the "schema-xxx-to-xxx.sql" file, followed by the "schema-xxx-to-xxx-cleanup.sql", before running the next upgrade path of version.
The wiki says that the cleanup file is there to clear any data needed during the update process, but looking at the content of some files, it has been used to remove column, drop constraint, add index keys (see setup/db/db/schema-410to420-cleanup.sql or setup/db/db/schema-430to440-cleanup.sql). Those are not things needed during an update process, but more related to the life of the schema.
IMO -cleanup.sql file should only contain commands if temporary tables/columns had to be created during an upgrade process, or the algorithm should be changed to follow the same behavior for any migration path schema-xxx-to-xxx.sql, followed by schema-xxx-to-xxx-cleanup.sql, then moving to the next upgrade version path.
To not change the content of the -cleanup.sql files, I suggest to change the upgrade logic.
In case, the wiki page should be changed accordingly to reflect the logic change (when PR is ready).