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" <[email protected]>
> To: "users" <[email protected]>
> 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
