[ https://issues.apache.org/jira/browse/CLOUDSTACK-10278?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16358387#comment-16358387 ]
Ernie Janse van Rensburg commented on CLOUDSTACK-10278: ------------------------------------------------------- proposed solution: delimiter ;; DROP PROCEDURE IF EXISTS `cloud`.`IDEMPOTENT_ADD_COLUMN`;; CREATE PROCEDURE `cloud`.`IDEMPOTENT_ADD_COLUMN` ( IN in_table_name VARCHAR(100) , IN in_column_name VARCHAR(100) , IN in_column_definition VARCHAR(100) ) BEGIN DECLARE CONTINUE HANDLER FOR 1060 BEGIN END; SET @ddl = CONCAT('ALTER TABLE cloud.', 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;; > 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)