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.x may encounter a critical failure during Stage 2 (Data Migration/Transfer portion). This failure halts the migration of the Update Manager (VUM) or vSphere Lifecycle Manager (vLCM) database schema.
"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
VMware vCenter Server 9.x

Cause

VA (virtual appliance) upgrade IDs persist within the vci_va_upgrade_files table of the Update Manager database. These stale records are from older Update Manager workflows and create foreign key constraint violations when the vCenter 9.x installer attempts to purge or update the schema.

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.

Note: Resetting the Update Manager database is a destructive task. Custom baselines, custom download settings, and manually imported patches/ISOs are removed and must be reapplied. Take an offline snapshot (powered-off state) of the vCenter Server and all linked vCenters in the ELM domain before proceeding. For environments with NSX-T deployed, do not reset the VUM database on clusters configured with vLCM cluster images without consulting Support.

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

Additional Information

To speak with a customer representative or a Support Engineer see Contact Support. Scroll to the bottom of the page and click on your respective region. See Contact Broadcom Support for more details.

Subscribe to this article for updates and fix status.