Database migration fails: cloud.ADD_GUEST_OS_AND_HYPERVISOR_MAPPING does not exist

[kohrar]

ISSUE TYPE

• Bug Report

COMPONENT NAME

Management

CLOUDSTACK VERSION

4.17.2.0  == upgrade to ==> 4.18.0.0 / 4.18.1.0

CONFIGURATION

N/A - standard install.

OS / ENVIRONMENT

Rocky Linux 8

SUMMARY

When attempting to upgrade from 4.17.2 to 4.18.0 (or 4.18.1), the management server fails to start due to the database upgrade failing.

Logs show the following:

Jan 22 12:04:27 nn1 java[324120]: INFO  [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) init = true                                                                                                                                                                                                                      
Jan 22 12:04:27 nn1 java[324120]: INFO  [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) DB version = 4.17.2.0 Code Version = 4.18.0.0                                                                                                                                                                                    
Jan 22 12:04:27 nn1 java[324120]: INFO  [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Database upgrade must be performed from 4.17.2.0 to 4.18.0.0                                                                                                                                                                     
Jan 22 12:04:27 nn1 java[324120]: ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:) Error executing: CALL ADD_GUEST_OS_AND_HYPERVISOR_MAPPING (1, 'AlmaLinux 9', 'KVM', 'default', 'AlmaLinux 9')                                                                                                                            
Jan 22 12:04:27 nn1 java[324120]: ERROR [c.c.u.d.ScriptRunner] (main:null) (logid:) java.sql.SQLSyntaxErrorException: PROCEDURE cloud.ADD_GUEST_OS_AND_HYPERVISOR_MAPPING does not exist                                                                                                                                     
Jan 22 12:04:27 nn1 java[324120]: ERROR [c.c.u.DatabaseUpgradeChecker] (main:null) (logid:) Unable to execute upgrade script                                                                                                                                                                                                 
Jan 22 12:04:27 nn1 java[324120]: java.sql.SQLSyntaxErrorException: PROCEDURE cloud.ADD_GUEST_OS_AND_HYPERVISOR_MAPPING does not exist                                                                                                                                                                                       
Jan 22 12:04:27 nn1 java[324120]:         at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)                                                                                                                                                                                                                
Jan 22 12:04:27 nn1 java[324120]:         at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87)                                                                                                                                                                                                                 
Jan 22 12:04:27 nn1 java[324120]:         at com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:226)                                                                                                                                                                                             
Jan 22 12:04:27 nn1 java[324120]:         at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:310)                                                                                                                                                                                               
Jan 22 12:04:27 nn1 java[324120]:         at com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:401)                                                                                                                                                                                                 
Jan 22 12:04:27 nn1 java[324120]:         at org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.java:64)                                                                                                                                                          
Jan 22 12:04:27 nn1 java[324120]:         at org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:54)                                                                                                                                                                   
Jan 22 12:04:27 nn1 java[324120]:         at org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:178)                                                                                                                                                                       
Jan 22 12:04:27 nn1 java[324120]:         at org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:54)                                                                                                                                                                     

... etc.

STEPS TO REPRODUCE

This happened on a test version of CloudStack 4.17.2. I don't remember whether this was a clean install of 4.17.0 (from scratch) or if it was upgraded from an even older version or if the database was restored. However, the database did not have any of the stored procedures that the migration seems to expect.

At this point, I'm not sure how the stored procedures were all lost on this instance.

EXPECTED RESULTS

On another instance of CloudStack, I do see the stored procedures.

mysql> 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 | 2022-06-14 14:59:42 | 2022-06-14 14:59:42 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
| cloud | IDEMPOTENT_ADD_COLUMN                        | PROCEDURE | cloud@localhost | 2022-06-14 14:59:29 | 2022-06-14 14:59:29 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
| cloud | IDEMPOTENT_CREATE_UNIQUE_INDEX               | PROCEDURE | cloud@localhost | 2022-03-14 15:54:35 | 2022-03-14 15:54:35 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
| cloud | IDEMPOTENT_DROP_FOREIGN_KEY                  | PROCEDURE | cloud@localhost | 2022-03-14 15:54:35 | 2022-03-14 15:54:35 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
| cloud | IDEMPOTENT_DROP_INDEX                        | PROCEDURE | cloud@localhost | 2022-03-14 15:54:35 | 2022-03-14 15:54:35 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
| cloud | IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING | PROCEDURE | cloud@localhost | 2022-03-14 15:54:33 | 2022-03-14 15:54:33 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |                                                                                              
+-------+----------------------------------------------+-----------+-----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+                                                                                              
6 rows in set (0.00 sec)                                                

ACTUAL RESULTS

mysql> show procedure status where Db='cloud';                                                                                                                                                                                                                                                                               
Empty set (0.00 sec)                                                                                                                                                                                                                                                                                                         

Workaround

I backed up (mysqldump --no-create-db --no-create-info --no-data --routines cloud > procedures.sql) / restored (mysql -u root cloud < procedures.sql) the stored procedures from the other instance of CloudStack we have running and then restored the cloud and cloud_usage databases before restarting the management server.

After doing that, I was able to restart the management service and have it upgrade/migrate properly.

[weizhouapache]

@kohrar
when you dump the database, you need to use mysqldump -R

  -R, --routines      Dump stored routines (functions and procedures).

[kohrar]

@kohrar when you dump the database, you need to use mysqldump -R

  -R, --routines      Dump stored routines (functions and procedures).

Yup. I believe that this omission at one point is likely the root cause of this issue.

Would the stored procedures be something that could be added into the migration for every version? I see some migrations drop and re-create procedures.

Alternatively, since the stored procedures don't really ever change, could CloudStack automatically add it back if it sees that it's missing during an upgrade?

[weizhouapache]

@kohrar when you dump the database, you need to use mysqldump -R

  -R, --routines      Dump stored routines (functions and procedures).

Yup. I believe that this omission at one point is likely the root cause of this issue.

Would the stored procedures be something that could be added into the migration for every version? I see some migrations drop and re-create procedures.

Alternatively, since the stored procedures don't really ever change, could CloudStack automatically add it back if it sees that it's missing during an upgrade?

yes @kohrar
this could be an improvement. cc @DaanHoogland

[DaanHoogland]

not sure what design is best, but a separate file per procedure as we have now designed for views as well, seems appropriate. good idea.

[yadvr]

@kohrar unfortunately as others have commented, this isn't a CloudStack issue as during db export/import the -R was missed so some of the non-table data are missing. You could run it the procedures manually by checking in CloudStack code and copy/running them manually.