Return-Path: X-Original-To: archive-asf-public-internal@cust-asf2.ponee.io Delivered-To: archive-asf-public-internal@cust-asf2.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by cust-asf2.ponee.io (Postfix) with ESMTP id 6434C200BC3 for ; Fri, 18 Nov 2016 14:39:00 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id 62FE0160AFE; Fri, 18 Nov 2016 13:39:00 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id B7A78160B16 for ; Fri, 18 Nov 2016 14:38:59 +0100 (CET) Received: (qmail 44825 invoked by uid 500); 18 Nov 2016 13:38:58 -0000 Mailing-List: contact issues-help@cloudstack.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: dev@cloudstack.apache.org Delivered-To: mailing list issues@cloudstack.apache.org Received: (qmail 44747 invoked by uid 500); 18 Nov 2016 13:38:58 -0000 Delivered-To: apmail-incubator-cloudstack-issues@incubator.apache.org Received: (qmail 44545 invoked by uid 99); 18 Nov 2016 13:38:58 -0000 Received: from arcas.apache.org (HELO arcas) (140.211.11.28) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 18 Nov 2016 13:38:58 +0000 Received: from arcas.apache.org (localhost [127.0.0.1]) by arcas (Postfix) with ESMTP id 865AE2C4C75 for ; Fri, 18 Nov 2016 13:38:58 +0000 (UTC) Date: Fri, 18 Nov 2016 13:38:58 +0000 (UTC) From: =?utf-8?Q?Marc-Aur=C3=A8le_Brothier_=28JIRA=29?= To: cloudstack-issues@incubator.apache.org Message-ID: In-Reply-To: References: Subject: [jira] [Updated] (CLOUDSTACK-9601) Database upgrade algorithm is incorrect MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394 archived-at: Fri, 18 Nov 2016 13:39:00 -0000 [ https://issues.apache.org/jira/browse/CLOUDSTACK-9601?page=3Dcom.atl= assian.jira.plugin.system.issuetabpanels:all-tabpanel ] Marc-Aur=C3=A8le Brothier updated CLOUDSTACK-9601: --------------------------------------------- Description:=20 The algorithm for the DB upgrade process in {{DatabaseUpgradeChecker}} is i= ncorrect since it can lead to different final state depending on the base v= ersion you are running it. The algo runs all {{schema-xxx-to-xxx.sql}} file= s, 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 vi= ew 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 pa= th of version. The wiki says that the cleanup file is there to clear any data needed durin= g 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 shoul= d be changed to follow the same behavior for any migration path {{schema-xx= x-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 i= ncorrect since it can lead to different final state depending on the base v= ersion you are running it. The algo runs all {{schema-xxx-to-xxx.sql}} file= s, 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 vi= ew 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 pa= th of version. The wiki says that the cleanup file is there to clear any data needed durin= g 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 shoul= d 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-960= 1 > Project: CloudStack > Issue Type: Bug > Security Level: Public(Anyone can view this level - this is the defa= ult.)=20 > 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=C3=A8le 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}} fi= les, and then {{schema-xxx-to-xxx-cleanup.sql}}. > For example if you have a change in a view in the file {{schema-460-to-46= 1.sql}} and another change prior to that version in the file {{schema-451-t= o-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 (whic= h 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-t= o-461.sql}}, which is the expected result. > The correct algorithm should run the "schema-xxx-to-xxx.sql" file, follow= ed 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 dur= ing the update process, but looking at the content of some files, it has be= en used to remove column, drop constraint, add index keys (see {{setup/db/d= b/schema-410to420-cleanup.sql}} or {{setup/db/db/schema-430to440-cleanup.sq= l}}). Those are not things needed during an update process, but more relate= d to the life of the schema. > IMO {{-cleanup.sql}} file should only contain commands if temporary table= s/columns had to be created during an upgrade process, or the algorithm sho= uld 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 movin= g to the next upgrade version path. -- This message was sent by Atlassian JIRA (v6.3.4#6332)