[jira] [Updated] (CLOUDSTACK-9601) Database upgrade algorithm is incorrect

["Marc-Aurèle Brothier (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CLOUDSTACK-9601?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Marc-Aurèle Brothier updated CLOUDSTACK-9601:
---------------------------------------------
    Description: 
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.

  was:
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 change to follow the same behavior for any migration path.


> Database upgrade algorithm is incorrect
> ---------------------------------------
>
>                 Key: CLOUDSTACK-9601
>                 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9601
>             Project: CloudStack
>          Issue Type: Bug
>      Security Level: Public(Anyone can view this level - this is the default.) 
>          Components: Upgrade
>    Affects Versions: 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
>            Reporter: Marc-Aurèle Brothier
>
> 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.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)