cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-10278) Adding a SQL table column is not Idempotent
Date Wed, 28 Mar 2018 11:20:00 GMT

    [ https://issues.apache.org/jira/browse/CLOUDSTACK-10278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16417184#comment-16417184
] 

ASF GitHub Bot commented on CLOUDSTACK-10278:
---------------------------------------------

DaanHoogland closed pull request #2449: CLOUDSTACK-10278 idempotent column addition
URL: https://github.com/apache/cloudstack/pull/2449
 
 
   

This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:

As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):

diff --git a/engine/schema/resources/META-INF/db/schema-41000to41100.sql b/engine/schema/resources/META-INF/db/schema-41000to41100.sql
index 2e7f9e9fe9c..4dc11d448fc 100644
--- a/engine/schema/resources/META-INF/db/schema-41000to41100.sql
+++ b/engine/schema/resources/META-INF/db/schema-41000to41100.sql
@@ -19,8 +19,54 @@
 -- Schema upgrade from 4.10.0.0 to 4.11.0.0
 --;
 
+--;
+-- Stored procedure to do idempotent column add;
+--;
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` (
+		IN in_table_name VARCHAR(200)
+    , IN in_column_name VARCHAR(200)
+    , IN in_column_definition VARCHAR(1000)
+)
+BEGIN
+
+    DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name);
SET @ddl = CONCAT(@ddl, ' ', 'ADD COLUMN') ; SET @ddl = CONCAT(@ddl, ' ', in_column_name);
SET @ddl = CONCAT(@ddl, ' ', in_column_definition); PREPARE stmt FROM @ddl; EXECUTE stmt;
DEALLOCATE PREPARE stmt; END;
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY` (
+		IN in_table_name VARCHAR(200)
+    , IN in_foreign_key_name VARCHAR(200)
+)
+BEGIN
+
+    DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('ALTER TABLE ', in_table_name);
SET @ddl = CONCAT(@ddl, ' ', ' DROP FOREIGN KEY '); SET @ddl = CONCAT(@ddl, ' ', in_foreign_key_name);
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_DROP_INDEX`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_DROP_INDEX` (
+		IN in_index_name VARCHAR(200)
+    , IN in_table_name VARCHAR(200)
+)
+BEGIN
+
+    DECLARE CONTINUE HANDLER FOR 1091 BEGIN END; SET @ddl = CONCAT('DROP INDEX ', in_index_name);
SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl = CONCAT(@ddl, ' ', in_table_name); PREPARE
stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt; END;
+
+DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX`;
+
+CREATE PROCEDURE `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX` (
+		IN in_index_name VARCHAR(200)
+    , IN in_table_name VARCHAR(200)
+    , IN in_index_definition VARCHAR(1000)
+)
+BEGIN
+
+    DECLARE CONTINUE HANDLER FOR 1061 BEGIN END; SET @ddl = CONCAT('CREATE UNIQUE INDEX ',
in_index_name); SET @ddl = CONCAT(@ddl, ' ', ' ON ') ; SET @ddl = CONCAT(@ddl, ' ', in_table_name);
SET @ddl = CONCAT(@ddl, ' ', in_index_definition); PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE
PREPARE stmt; END;
+
 -- Add For VPC flag
-ALTER TABLE cloud.network_offerings ADD COLUMN for_vpc INT(1) NOT NULL DEFAULT 0;
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.network_offerings','for_vpc', 'INT(1) NOT NULL
DEFAULT 0');
+
 UPDATE cloud.network_offerings o
 SET for_vpc = 1
 where
@@ -88,7 +134,7 @@ CREATE TABLE IF NOT EXISTS `cloud`.`annotations` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 DROP VIEW IF EXISTS `cloud`.`last_annotation_view`;
-CREATE VIEW `last_annotation_view` AS
+CREATE VIEW `cloud`.`last_annotation_view` AS
     SELECT
         `annotations`.`uuid` AS `uuid`,
         `annotations`.`annotation` AS `annotation`,
@@ -405,21 +451,18 @@ UPDATE `cloud`.`monitoring_services` SET pidfile="/var/run/apache2/apache2.pid"
 UPDATE `cloud`.`vm_template` SET guest_os_id=99 WHERE id=8;
 
 -- Network External Ids
-ALTER TABLE `cloud`.`networks` ADD `external_id` varchar(255);
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.networks','external_id', 'varchar(255)');
 
 -- Separate Subnet for CPVM and SSVM (system vms)
-ALTER TABLE `cloud`.`op_dc_ip_address_alloc`
-ADD COLUMN `forsystemvms` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'Indicates if IP is dedicated
for CPVM or SSVM';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.op_dc_ip_address_alloc','forsystemvms', 'TINYINT(1)
NOT NULL DEFAULT 0 COMMENT ''Indicates if IP is dedicated for CPVM or SSVM'' ');
 
-ALTER TABLE `cloud`.`op_dc_ip_address_alloc`
-ADD COLUMN `vlan` INT(10) UNSIGNED NULL COMMENT 'Vlan the management network range is on';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.op_dc_ip_address_alloc','vlan', 'INT(10) UNSIGNED
NULL COMMENT ''Vlan the management network range is on'' ');
 
 -- CLOUDSTACK-4757: Support multidisk OVA
-ALTER TABLE `cloud`.`vm_template` ADD COLUMN `parent_template_id` bigint(20) unsigned DEFAULT
NULL COMMENT 'If datadisk template, then id of the root template this template belongs to';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.vm_template','parent_template_id', 'bigint(20)
unsigned DEFAULT NULL COMMENT ''If datadisk template, then id of the root template this template
belongs to'' ');
 
 -- CLOUDSTACK-10146: Bypass Secondary Storage for KVM templates
-ALTER TABLE `cloud`.`vm_template`
-ADD COLUMN `direct_download` TINYINT(1) DEFAULT '0' COMMENT 'Indicates if Secondary Storage
is bypassed and template is downloaded to Primary Storage';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.vm_template','direct_download', 'TINYINT(1) DEFAULT
0 COMMENT ''Indicates if Secondary Storage is bypassed and template is downloaded to Primary
Storage'' ');
 
 -- Changes to template_view for both multidisk OVA and bypass secondary storage for KVM templates
 DROP VIEW IF EXISTS `cloud`.`template_view`;
@@ -528,8 +571,7 @@ CREATE VIEW `cloud`.`template_view` AS
              OR (`resource_tags`.`resource_type` = 'ISO')))));
 
 -- CLOUDSTACK-10109: Enable dedication of public IPs to SSVM and CPVM
-ALTER TABLE `cloud`.`user_ip_address`
-ADD COLUMN `forsystemvms` TINYINT(1) NOT NULL DEFAULT '0' COMMENT 'true if IP is set to system
vms, false if not';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.user_ip_address','forsystemvms', 'TINYINT(1)
NOT NULL DEFAULT 0 COMMENT ''true if IP is set to system vms, false if not'' ');
 
 -- ldap binding on domain level
 CREATE TABLE IF NOT EXISTS `cloud`.`domain_details` (
@@ -541,11 +583,11 @@ CREATE TABLE IF NOT EXISTS `cloud`.`domain_details` (
     CONSTRAINT `fk_domain_details__domain_id` FOREIGN KEY (`domain_id`) REFERENCES `domain`(`id`)
ON DELETE CASCADE
 )ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-ALTER TABLE cloud.ldap_configuration ADD COLUMN domain_id BIGINT(20) DEFAULT NULL;
-ALTER TABLE cloud.ldap_trust_map ADD COLUMN account_id BIGINT(20) DEFAULT 0;
-ALTER TABLE cloud.ldap_trust_map DROP FOREIGN KEY fk_ldap_trust_map__domain_id;
-DROP INDEX uk_ldap_trust_map__domain_id ON cloud.ldap_trust_map;
-CREATE UNIQUE INDEX uk_ldap_trust_map__bind_location ON ldap_trust_map (domain_id, account_id);
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.ldap_configuration','domain_id', 'BIGINT(20)
DEFAULT NULL');
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.ldap_trust_map','account_id', 'BIGINT(20) DEFAULT
0');
+CALL `cloud`.`IDEMPOTENT_DROP_FOREIGN_KEY`('cloud.ldap_trust_map','fk_ldap_trust_map__domain_id');
+CALL `cloud`.`IDEMPOTENT_DROP_INDEX`('uk_ldap_trust_map__domain_id','cloud.ldap_trust_map');
+CALL `cloud`.`IDEMPOTENT_CREATE_UNIQUE_INDEX`('uk_ldap_trust_map__bind_location','cloud.ldap_trust_map',
'(domain_id, account_id)');
 
 CREATE TABLE IF NOT EXISTS `cloud`.`netscaler_servicepackages` (
   `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
@@ -565,5 +607,5 @@ CREATE TABLE IF NOT EXISTS `cloud`.`external_netscaler_controlcenter`
(
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
-ALTER TABLE `cloud`.`sslcerts` ADD COLUMN `name` varchar(255) NULL default NULL COMMENT 'Name
of the Certificate';
-ALTER TABLE `cloud`.`network_offerings` ADD COLUMN `service_package_id` varchar(255) NULL
default NULL COMMENT 'Netscaler ControlCenter Service Package';
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.sslcerts','name', 'varchar(255) NULL default
NULL COMMENT ''Name of the Certificate'' ');
+CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.network_offerings','service_package_id', 'varchar(255)
NULL default NULL COMMENT ''Netscaler ControlCenter Service Package'' ');
\ No newline at end of file


 

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


> Adding a SQL table column is not Idempotent
> -------------------------------------------
>
>                 Key: CLOUDSTACK-10278
>                 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-10278
>             Project: CloudStack
>          Issue Type: Bug
>      Security Level: Public(Anyone can view this level - this is the default.) 
>          Components: Install and Setup
>    Affects Versions: 4.10.0.0, 4.11.0.0
>            Reporter: Ernie Janse van Rensburg
>            Assignee: Ernie Janse van Rensburg
>            Priority: Major
>   Original Estimate: 4h
>  Remaining Estimate: 4h
>
> The SQL code to add a new column to a table in the META-INF/db/schema-41000to41100.sql
script is not written in an idempotent way. When the upgrade is re-run, the code above causes
a SQL error as reported on the user mailing list: 
> ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
> Error executing: ALTER TABLE cloud.network_offerings ADD COLUMN for_vpc
> INT(1) NOT NULL DEFAULT 0
> This is a more generic problem for every version due to to the fact that it is not idempotent
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message