vCenter 9.0 Upgrade fails on Stage 2 with message, "Error attempting Upgrade Database Schema"

Products

• VMware vCenter Server
• VMware Cloud Foundation

Issue/Introduction

On the failed 9.x vCenter appliance, you see the following error in the import-upgrade-runner.log

/var/log/vmware/upgrade/import-upgrade-runner.log
======================================================================================
INFO upgrade.states.component_states vcIntegrity:Import:HH:MM:SS verbose vmware-vci-vciInstallUtils[54261] [Originator@6876 sub=Default] [VdbODBCError::VdbODBCError] SQLGetDiagRec SQLState='#####' NativeError=1 MessageText='ERROR: update or delete on table "vci_va_upgrades" violates foreign key constraint "fk_va_upg_pkg_ref_upg" on table "vci_va_upgrade_files"
INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (upgrade_id)=(###) is still referenced from table "vci_va_upgrade_files".;
INFO upgrade.states.component_states vcIntegrity:Import: --> Error while executing the query' rc=0
INFO upgrade.states.component_states vcIntegrity:Import: YYYY-MM-DDTHH:MM:SS error vmware-vci-vciInstallUtils[#####] [Originator@6876 sub=Default] [installerRunDBCommand 627]  [DATABASE] Vdb::RunScript, SQL Exec Error: "ODBC error: (23503) - ERROR: update or delete on table "vci_va_upgrades" violates foreign key constraint "fk_va_upg_pkg_ref_upg" on table "vci_va_upgrade_files"
INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (upgrade_id)=(###) is still referenced from table "vci_va_upgrade_files".;
INFO upgrade.states.component_states vcIntegrity:Import: --> Error while executing the query" is returned when executing SQL statement "DELETE FROM vci_updates
INFO upgrade.states.component_states vcIntegrity:Import: --> where id not in (select update_id from vci_update_packages)"

Environment

VCF 9.x
vCenter 9.x

Cause

The error stems from VA(virtual appliance) upgrade IDs persisting within the VCI_VA_upgrade_files section of the Update Manager database tables. This issue is linked to the older Update Manager workflow responsible for managing virtual appliance upgrades. 

Resolution

Process to Reset the VMware Update Manager Database on a VCSA

Caution: Resetting the Update Manager database is a destructive task. Custom baselines (but not Cluster Images), custom download settings, and manually imported patches/ISOs will be removed and will need to be reapplied following the reset. Before applying the steps below, take a backup and an offline-snapshot (in powered-off state) of the vCenter Server. If the vCenter Server is part of an ELM environment, take a snapshot or a backup of all vCenters within the ELM domain. Note all of the custom configurations within Update Manager - e.g., proxy settings, third-party download URLs, customized baselines, etc. - before proceeding.

Workaround #1

1. Power off and remove the failed 9.x vCenter appliance
   
   
2. Power on the 8.x vCenter
   
   
3. SSH into the 8.x vCenter
   
   
4. Reset the VUM Database using KB Resetting the VMware Update Manager Database
   
   
5. Retry the vCenter Upgrade to VCF 9.0

For environments where NSX-T is deployed, resetting the VMware Update Manager database will result in clusters configured with vLCM cluster images (steady-state clusters) being unable to save or validate the image associated with them. 
Do not reset the VMware Update Manager database if NSX-T is installed on a cluster where a cluster image is configured without the help of VMware Support.

Workaround #2

1. Power off and remove the failed vCenter appliance 9.x
   
   
2. Power on the 8.x vCenter
   
   
3. SSH into the 8.x vCenter
   
   
4. Record postgres DB password
   
   cat /usr/lib/vmware-updatemgr/bin/configvalues.txt
   
   
5. Elevate to updatemgr user 
   
   su updatemgr -s /bin/bash
   
   
6. Connect to the VCDB as VUM user
   
   psql -U vumuser -d VCDB
   
   
7. Delete entries in vci_va_upgrade_files table for offending 'upgrade_id' found in the import-upgrade-runner.log
   
   Example /var/log/vmware/upgrade/import-upgrade-runner.log

INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (upgrade_id)=(455) is still referenced from table "vci_va_upgrade_files".;

DELETE FROM vci_va_upgrade_files WHERE upgrade_id = <upgrade_id>;
   
   
8. Delete entries in vci_va_upgrades table for offending 'upgrade_id' found in the import-upgrade-runner.log
   
   DELETE FROM vci_va_upgrades WHERE upgrade_id = <upgrade_id>;
   
   
9. Quit from the VUM DB
   
   /q
   
   
10. Exit out of the updatemgr shell.
    
    exit
    
    
11. Retry the Upgrade to VCF 9.0