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

Products

VMware vCenter Server VMware Cloud Foundation

Issue/Introduction

Upgrade of vCenter Server to 9.0.2.0 fails with the error " Error attempting Upgrade Database Schema Resolution. Please check vcIntegrity migration logs for details."

On the failed 9.x vCenter appliance, you see the following error in the /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

VMware Cloud Foundation 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

To resolve the vCenter 9.0 upgrade failure, reset the VMware Update Manager database on the source 8.x appliance or manually delete orphaned records from the vci_va_upgrade_files and vci_va_upgrades tables before retrying the upgrade vCenter 9.0 Upgrade fails.

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

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

Note: 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

Power off and remove the failed vCenter appliance 9.x
Power on the 8.x vCenter
SSH into the 8.x vCenter

Record postgres DB password

cat /usr/lib/vmware-updatemgr/bin/configvalues.txt

Elevate to updatemgr user
```
su updatemgr -s /bin/bash
```
Connect to the VCDB as VUM user
```
psql -U vumuser -d VCDB
```

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>;

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>;
```
Quit from the VUM DB
```
\q
```
Exit out of the updatemgr shell.
```
exit
```
Retry the Upgrade to VCF 9.0