"Error attempting Upgrade Database Schema" during vCenter 8.0 U3x to 9.1.0.0 upgrade

Products

• VMware vCenter Server

Issue/Introduction

• When upgrading vCenter Server from 8.0 U3x to 9.1.0.0, the process fails during the vcIntegrity import phase while attempting a database schema upgrade.
  
  Error attempting Upgrade Database Schema
Please check vcintegrity migration logs for details.
  
  
  
  
  
• On the source vCenter server the following error is logged in,  /var/log/vmware/upgrade/Import_com.vmware.vcIntegrity_####_##_##_##_##.log

YYYY-MM-DDTHH:MM:SS DEBUG vmware-vci-vciInstallUtils 39747 [vc@4413 sub="Default"] [VdbODBCError::VdbODBCError] SQLGetDiagRec SQLState='####' NativeError=1 MessageText='ERROR: insert or update on table "pm_recommendation_spec" violates foreign key constraint "fk_pm_recommendation_spec"
--> DETAIL: Key (entity_id, spec_id)=(domain-##, -1000) is not present in table "pm_recommendation_info".;
--> Error while executing the query' rc=0
YYYY-MM-DDTHH:MM:SS  ERROR vmware-vci-vciInstallUtils 39747 [vc@4413 sub="Default"] [installerRunDBCommand 626]  [DATABASE] Vdb::RunScript, SQL Exec Error: "ODBC error: (23503) - ERROR: insert or update on table "pm_recommendation_spec" violates foreign key constraint "fk_pm_recommendation_spec"
--> DETAIL: Key (entity_id, spec_id)=(domain-##, -1000) is not present in table "pm_recommendation_info".;
--> Error while executing the query" is returned when executing SQL statement "ALTER TABLE IF EXISTS PM_RECOMMENDATION_SPEC
-->     ADD CONSTRAINT FK_PM_RECOMMENDATION_SPEC
-->         FOREIGN KEY (ENTITY_ID, SPEC_ID)
-->         REFERENCES PM_RECOMMENDATION_INFO (ENTITY_ID, SPEC_ID)
-->         ON DELETE CASCADE"

YYYY-MM-DDTHH:MM:SS INFO vcIntegrity_upgrade Upgrade Database Schema StdErr:
YYYY-MM-DDTHH:MM:SS  INFO vcIntegrity_upgrade
YYYY-MM-DDTHH:MM:SS  ERROR __main__ Upgrade Phase 'vcIntegrity:Import' failed. Exception: Error attempting Upgrade Database Schema
Traceback (most recent call last):
  File "/usr/lib/vmware/cis_upgrade_runner/payload/componentPhaseLauncher.py", line 461, in main
    executionResult = systemExtension(exeContext)
upgrade_errors.PermanentError: Error attempting Upgrade Database Schema


 

Environment

VMware Cloud Foundation 9.1
VMware vSphere Foundation 9.1

Cause

The failure is caused by orphaned database records in the vcIntegrity database. Specifically, the pm_recommendation_spec table contains stale entries that lack corresponding parent records in pm_recommendation_info.

vCenter 9.1.0.0 introduces a strict foreign key constraint (FK_PM_RECOMMENDATION_SPEC) to harden database integrity. The Postgres engine blocks this schema change because existing orphaned data violates the constraint, throwing a fatalerror.

Resolution

Broadcom is aware of this issue, and a fix will be included in a future release of VMware Cloud Foundation 

Workaround:

1. Take a snapshot of the source vCenter appliance before proceeding.

2. Download the cleanup_rec_orphans.py script attached to this KB.

3. Copy the script to the vCenter server, placing it in the /tmp directory.
   
   Note: You can use an SCP client like WinSCP to transfer the file. If you encounter a connection or protocol error while uploading files to the appliance, please refer to  Connecting to vCenter Server Virtual Appliance using WinSCP fails with the error: Received too large (1433299822 B) SFTP packet. Max supported packet size is 1024000 B
4. Log in to the vCenter server via SSH using root privileges.
5. Perform a Dry-Run Audit (Read-Only) to identify any orphans by running the following command:
   
   su updatemgr -s /bin/bash -c "python3 /tmp/cleanup_rec_orphans.py"
   
   
6. Execute the cleanup by running the script with the execute flag:
   
   su updatemgr -s /bin/bash -c "python3 /tmp/cleanup_rec_orphans.py --execute"
7. Retry the vCenter upgrade.