You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cloudstack.apache.org by Andrei Mikhailovsky <an...@arhont.com.INVALID> on 2024/08/15 19:40:07 UTC
Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
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
Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
Posted by Andrei Mikhailovsky <an...@arhont.com.INVALID>.
Further to my previous message i've done some more digging on the DB side:
SHOW PROCEDURE STATUS WHERE Db = 'cloud_usage';
Empty set (0.001 sec)
Doesn't look like there are any procedures in the cloud_usage database.
MariaDB [cloud_usage]> show tables;
+----------------------------+
| Tables_in_cloud_usage |
+----------------------------+
| account |
| bucket_statistics |
| cloud_usage |
| quota_account |
| quota_balance |
| quota_credits |
| quota_email_templates |
| quota_tariff |
| quota_usage |
| usage_backup |
| usage_event |
| usage_event_details |
| usage_ip_address |
| usage_job |
| usage_load_balancer_policy |
| usage_network |
| usage_network_offering |
| usage_networks |
| usage_port_forwarding |
| usage_security_group |
| usage_snapshot_on_primary |
| usage_storage |
| usage_vm_disk |
| usage_vm_instance |
| usage_vmsnapshot |
| usage_volume |
| usage_vpc |
| usage_vpn_user |
| user_statistics |
| vm_disk_statistics |
+----------------------------+
30 rows in set (0.000 sec)
There are a lot of tables and the backup db size is over 40mb.
In contrast, the 'cloud' database does contain a bunch of procedures, including the IDEMPOTENT_ADD_COLUMN which is not present in the cloud_usage db which seems to be the cause of the db schema upgrade:
SHOW PROCEDURE STATUS WHERE Db = 'cloud';
+-------+-------------------------------------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+-------------------------------------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| cloud | ADD_GUEST_OS_AND_HYPERVISOR_MAPPING | PROCEDURE | cloud@localhost | 2023-04-17 19:12:20 | 2023-04-17 19:12:20 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_ADD_COLUMN | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_ADD_FOREIGN_KEY | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_ADD_KEY | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_ADD_UNIQUE_KEY | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_CHANGE_COLUMN | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
| cloud | IDEMPOTENT_DROP_FOREIGN_KEY | PROCEDURE | cloud@localhost | 2023-04-17 19:21:55 | 2023-04-17 19:21:55 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+-------+-------------------------------------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
7 rows in set (0.001 sec)
Any advice on what I am missing and how to get it fixed?
Cheers
Andrei
----- Original Message -----
> From: "Andrei Mikhailovsky" <an...@arhont.com.INVALID>
> To: "users" <us...@cloudstack.apache.org>
> Sent: Thursday, 15 August, 2024 21:40:07
> Subject: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
> 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
Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
Posted by Andrei Mikhailovsky <an...@arhont.com.INVALID>.
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" <an...@arhont.com.INVALID>
> To: "users" <us...@cloudstack.apache.org>
> 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" <jo...@scclouds.com.br>
>> To: "users" <us...@cloudstack.apache.org>
>> 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
>>>
Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
Posted by Andrei Mikhailovsky <an...@arhont.com.INVALID>.
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" <jo...@scclouds.com.br>
> To: "users" <us...@cloudstack.apache.org>
> 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
>>
>>
Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
Posted by Andrei Mikhailovsky <an...@arhont.com.INVALID>.
Thanks Joao,
I will try it and revert back.
Cheers
----- Original Message -----
> From: "João Jandre Paraquetti" <jo...@scclouds.com.br>
> To: "users" <us...@cloudstack.apache.org>
> 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
>>
>>
Re: Upgrade from 4.19.0.1 to 4.19.1.1 fails due to DB schema upgrade errors
Posted by João Jandre Paraquetti <jo...@scclouds.com.br>.
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
>
>
>