New install - 4.19.1 on Centos Stream 9, database upgrade fails. #9649

ISSUE TYPE

• Bug Report

COMPONENT NAME

Core startup

CLOUDSTACK VERSION

4.19.1

CONFIGURATION

Separate database server:
mysql> select @@Version
-> ;
+-----------------+
| @@Version |
+-----------------+
| 10.5.22-MariaDB |
+-----------------+
1 row in set (0.00 sec)

OS / ENVIRONMENT

CentOS Stream 9

SUMMARY

First startup, system detects that database is not correct revision ...
2024-09-06 22:05:47,734 INFO [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) DB version = 4.0.0 Code Version = 4.19.1.1
2024-09-06 22:05:47,735 INFO [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Database upgrade must be performed from 4.0.0 to 4.19.1.1
2024-09-06 22:05:47,751 DEBUG [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Running upgrade Upgrade40to41 to upgrade from 4.0.0-4.1.0 to 4.1.0

During the upgrade of the database schema, there is an invalid operation attempted (altering a foreign key column) which is not allowed unless you turn of foreign key constraints.

2024-09-06 22:05:47,796 DEBUG [c.c.u.d.ScriptRunner] (main:null) (logid:) alter table storage_pool modify id bigint unsigned AUTO_INCREMENT UNIQUE NOT NULL
2024-09-06 22:05:47,817 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:) Error executing: alter table storage_pool modify id bigint unsigned AUTO_INCREMENT UNIQUE NOT NULL
2024-09-06 22:05:47,817 ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:) java.sql.SQLException: Cannot change column 'id': used in a foreign key constraint 'fk_storage_pool_details__pool_id' of table 'cloud.storage_pool_details'
2024-09-06 22:05:47,819 ERROR [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Unable to execute upgrade script
java.sql.SQLException: Cannot change column 'id': used in a foreign key constraint 'fk_storage_pool_details__pool_id' of table 'cloud.storage_pool_details'
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)

STEPS TO REPRODUCE

EXPECTED RESULTS

I expected the web site to come available.

ACTUAL RESULTS

<html>
<body>
<!--StartFragment--><h2 style="color: rgb(0, 0, 0); font-family: &quot;Times New Roman&quot;; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;">HTTP ERROR 503 Service Unavailable</h2>
URI: | /client/
-- | --
503
Service Unavailable
-

<!--EndFragment-->
</body>
</html>