Error: Upgrade Database Schema fails during vCenter upgrade

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). This failure halts the migration of the Update Manager (VUM) or vSphere Lifecycle Manager (vLCM) database schema.

Upgrade fails during the IMPORT phase with: Error attempting Upgrade Database Schema.

The following was logged in the vCenter Server's /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 (VCF) 9.x
VMware vCenter Server 8.x

Cause

Virtual appliance (VA) upgrade IDs from legacy vSphere workflows persist within the "vci_va_upgrade_files" and "vci_va_upgrades" table hierarchy of the Update Manager database. These stale records create foreign key constraint violations when the vCenter installer attempts to purge or update the schema. The cleanup scripts fail to remove the records, resulting in a fatal SQL error during the migration.

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 (excluding Cluster Images), custom download settings, and manually imported patches/ISOs will be permanently removed and must be reapplied afterward.

Prerequisites & Critical Steps Before Proceeding:

Document Configurations: Note all custom configurations within Update Manager, such as proxy settings, third-party download URLs, and customized baselines.
Take Backups/Snapshots: Take a complete backup and an offline snapshot (powered-off state) of the vCenter Server. If the server belongs to an Enhanced Linked Mode (ELM) environment, take snapshots or backups of all vCenters within the ELM domain.
NSX-T Environments: If NSX-T is deployed, resetting the database will break the ability of clusters configured with vLCM cluster images (steady-state clusters) to save or validate their images. Do not proceed on these clusters without consulting Broadcom Support.

Workaround #1 Process to Reset the VMware Update Manager Database on a VCSA:

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

Workaround #2 Manual Record Deletion:

Power off and remove the failed 9.x vCenter appliance.
Power on the 8.x vCenter and take an offline snapshot.
SSH into the 8.x vCenter as root.
Elevate to updatemgr user:
```
su updatemgr -s /bin/bash
```
Connect to the database:
```
psql -U vumuser -d VCDB
```
Execute the following commands in this exact order
Note: Replace #### with the ID from the logs "INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (upgrade_id)=(###) is still referenced from table "vci_va_upgrade_files".;"
```
DELETE FROM vci_va_upgrade_files WHERE upgrade_id = ####;
DELETE FROM vci_va_upgrade_changelog WHERE upgrade_id = ####;
DELETE FROM vci_va_upgrades WHERE upgrade_id = ####;
```
Exit the database:
```
 \q
```
Retry the vCenter Upgrade.

Additional Information

To speak with a customer representative or Support Engineer, visit Contact Support. Scroll to the bottom of the page, and select your region. For additional information, refer Contact Broadcom Support for more details.

Subscribe to this article for updates and fix status.