You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@cloudstack.apache.org by "Ernie Janse van Rensburg (JIRA)" <ji...@apache.org> on 2018/02/09 13:42:00 UTC
[jira] [Commented] (CLOUDSTACK-10278) Adding a SQL table column is
not Idempotent
[ 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)