Joao, i spoke too soon. with a bit of Claude help i got the following sql query
that worked:
DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`;
DELIMITER //
CREATE PROCEDURE `cloud_usage`.`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 //
DELIMITER ;
After restarting the management server it looks like the problem is solved.
thanks for your help
Cheers
Andrei
----- Original Message -----
> From: "Andrei Mikhailovsky" <[email protected]>
> To: "users" <[email protected]>
> Sent: Thursday, 15 August, 2024 22:27:58
> Subject: Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade
> errors
> Hi Joao,
>
> Do you have a link to an unformatted sql syntax for creating the procedure as
> when I copy/paste i get a bunch of sql errors.
>
> Cheers
>
> ----- Original Message -----
>> From: "João Jandre Paraquetti" <[email protected]>
>> To: "users" <[email protected]>
>> Sent: Thursday, 15 August, 2024 22:13:53
>> Subject: Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade
>> errors
>
>> Hello, Andrei
>>
>> This is happening because the procedure IDEMPOTENT_ADD_COLUMN does not
>> exist in your cloud_usage DB. You can create it manually with the
>> following queries:
>>
>> DROP PROCEDURE IF EXISTS `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`;
>> CREATE PROCEDURE `cloud_usage`.`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;
>>
>> After defining the procedure, you will have to restart your
>> cloudstack-management service.
>>
>> This type of issue will should not happen in the next releases, as
>> https://github.com/apache/cloudstack/pull/9385 reworked how procedures
>> are defined for ACS DBs.
>>
>> Best regards,
>>
>> João Jandre
>>
>> On 8/15/24 16:40, Andrei Mikhailovsky wrote:
>>> Hello all,
>>>
>>> I've tried to upgrade my cloudstack from 4.19.0.1 to the latest 4.19.1.1. I
>>> am
>>> running Ubuntu Server 20.04 with latest updates using Galera +
>>> mariadb-server
>>> version 10.3.39-0ubuntu0.20.04.2. the cloudstack installation is pretty old
>>> (over 10+ years old) and I have been upgrading every major version release
>>> and
>>> most of the minor version releases too.
>>>
>>> after the cloudstack-* packages were installed to the latest version and the
>>> cloudstack-management server restarted I have the following information in
>>> the
>>> management-server.log file:
>>>
>>> 2024-08-15 19:58:06,899 INFO [c.c.u.DatabaseUpgradeChecker] (main:null)
>>> (logid:)
>>> DB version = 4.19.0.1 Code Version = 4.19.1.1
>>> 2024-08-15 19:58:06,899 INFO [c.c.u.DatabaseUpgradeChecker] (main:null)
>>> (logid:)
>>> Database upgrade must be performed from 4.19.0.1 to 4.19.1.1
>>> 2024-08-15 19:58:06,969 DEBUG [c.c.u.DatabaseUpgradeChecker] (main:null)
>>> (logid:) Running upgrade Upgrade41900to41910 to upgrade from
>>> 4.19.0.0-4.19.1.0
>>> to 4.19.1.0
>>> 2024-08-15 19:58:06,971 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:) --
>>> Schema upgrade from 4.19.0.0 to 4.19.1.0
>>> 2024-08-15 19:58:06,972 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:) --
>>> Updates the populated Quota tariff's types VM_DISK_BYTES_READ,
>>> VM_DISK_BYTES_WRITE, VM_DISK_IO_READ and VM_DISK_IO_WRITE to the correct
>>> unit.
>>> 2024-08-15 19:58:06,972 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> UPDATE
>>> cloud_usage.quota_tariff SET usage_unit = 'Bytes', updated_on = NOW() WHERE
>>> effective_on = '2010-05-04 00:00:00' AND name IN ('VM_DISK_BYTES_READ',
>>> 'VM_DISK_BYTES_WRITE')
>>> 2024-08-15 19:58:06,972 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> UPDATE
>>> cloud_usage.quota_tariff SET usage_unit = 'IOPS', updated_on = NOW() WHERE
>>> effective_on = '2010-05-04 00:00:00' AND name IN ('VM_DISK_IO_READ',
>>> 'VM_DISK_IO_WRITE')
>>> 2024-08-15 19:58:06,973 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> -- PR
>>> #7236 - [Usage] Create network billing
>>> 2024-08-15 19:58:06,973 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> CREATE
>>> TABLE IF NOT EXISTS `cloud_usage`.`usage_networks` ( `id` bigint(20)
>>> unsigned
>>> NOT NULL AUTO_INCREMENT, `network_offering_id` bigint(20) unsigned NOT NULL,
>>> `zone_id` bigint(20) unsigned NOT NULL, `network_id` bigint(20) unsigned NOT
>>> NULL, `account_id` bigint(20) unsigned NOT NULL, `domain_id` bigint(20)
>>> unsigned NOT NULL, `state` varchar(100) DEFAULT NULL, `removed` datetime
>>> DEFAULT NULL, `created` datetime NOT NULL, PRIMARY KEY (`id`) )
>>> ENGINE=InnoDB
>>> CHARSET=utf8
>>> 2024-08-15 19:58:06,990 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:) --
>>> allow for bigger urls
>>> 2024-08-15 19:58:06,990 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> ALTER
>>> TABLE `cloud`.`vm_template` MODIFY COLUMN `url` VARCHAR(1024) DEFAULT NULL
>>> COMMENT 'the url where the template exists externally'
>>> 2024-08-15 19:58:06,996 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> -- PR
>>> #7235 - [Usage] Create VPC billing
>>> 2024-08-15 19:58:06,997 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> CREATE
>>> TABLE IF NOT EXISTS `cloud_usage`.`usage_vpc` ( `id` bigint(20) unsigned NOT
>>> NULL AUTO_INCREMENT, `vpc_id` bigint(20) unsigned NOT NULL, `zone_id`
>>> bigint(20) unsigned NOT NULL, `account_id` bigint(20) unsigned NOT NULL,
>>> `domain_id` bigint(20) unsigned NOT NULL, `state` varchar(100) DEFAULT NULL,
>>> `created` datetime NOT NULL, `removed` datetime DEFAULT NULL, PRIMARY KEY
>>> (`id`) ) ENGINE=InnoDB CHARSET=utf8
>>> 2024-08-15 19:58:07,004 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> CALL
>>> `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`('cloud_usage.cloud_usage', 'state',
>>> 'VARCHAR(100) DEFAULT NULL')
>>>
>>> 2024-08-15 19:58:07,014 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> Error
>>> executing: CALL
>>> `cloud_usage`.`IDEMPOTENT_ADD_COLUMN`('cloud_usage.cloud_usage', 'state',
>>> 'VARCHAR(100) DEFAULT NULL')
>>> 2024-08-15 19:58:07,015 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:)
>>> java.sql.SQLSyntaxErrorException: PROCEDURE
>>> cloud_usage.IDEMPOTENT_ADD_COLUMN
>>> does not exist
>>> 2024-08-15 19:58:07,015 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
>>> (logid:) Unable to execute upgrade script
>>>
>>> 2024-08-15 19:58:07,015 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null)
>>> (logid:) Unable to execute upgrade script
>>> java.sql.SQLSyntaxErrorException: PROCEDURE
>>> cloud_usage.IDEMPOTENT_ADD_COLUMN
>>> does not exist
>>> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
>>> at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87)
>>> at
>>> com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:236)
>>> at
>>> com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:320)
>>> at
>>> com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:435)
>>> at
>>> org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.java:64)
>>> at
>>> org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:54)
>>> at
>>> org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:178)
>>> at
>>> org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:54)
>>> at
>>> org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:356)
>>> at java.base/java.lang.Iterable.forEach(Iterable.java:75)
>>>
>>> [output removed]
>>>
>>>
>>>
>>> As a result, I get the following page instead of the login prompt:
>>>
>>> HTTP ERROR 503 Service Unavailable
>>> URI: /client/
>>> STATUS: 503
>>> MESSAGE: Service Unavailable
>>> SERVLET: -
>>>
>>>
>>> Could someone please help me with fixing the problem?
>>>
>>> Cheers
>>>
>>> Andrei
>>>
