VMware vCenter multi-step upgrade from 6.x to directly 7.0 Update2 OR upgrade from 6.x via 7.0 GA/Update1 to 7.0 Update2 fails with error "Error attempting Upgrade Database Schema".

Products

• VMware vCenter Server

Issue/Introduction

VMware vCenter multi-step upgrade from 6.x to directly 7.0 Update2 OR upgrade from 6.x via 7.0 GA/Update1 to 7.0 Update2 fails with error "Error attempting Upgrade Database Schema".

In the /var/log/vmware/upgrade/import-upgrade-runner.log
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: 2021-07-16T05:10:58.210Z verbose vmware-vci-vciInstallUtils[45012] [Originator@6876 sub=Default] Execute: ALTER TABLE IF EXISTS vci_scanresults_targets ADD CONSTRAINT fk_vci_sresvms_ref_sig
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> FOREIGN KEY (update_id) REFERENCES vci_updates(id) ON DELETE CASCADE
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: 2021-07-16T05:10:58.219Z verbose vmware-vci-vciInstallUtils[45012] [Originator@6876 sub=Default] [VdbODBCError::VdbODBCError] SQLGetDiagRec SQLState='23503' NativeError=1 MessageText='ERROR: insert or update on table "vci_scanresults_targets" violates foreign key constraint "fk_vci_sresvms_ref_sig"
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (update_id)=(516) is not present in table "vci_updates".;
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> Error while executing the query' rc=0
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: 2021-07-16T05:10:58.227Z error vmware-vci-vciInstallUtils[45012] [Originator@6876 sub=Default] [installerRunDBCommand 556] [DATABASE] Vdb::RunScript, SQL Exec Error: "ODBC error: (23503) - ERROR: insert or update on table "vci_scanresults_targets" violates foreign key constraint "fk_vci_sresvms_ref_sig"
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> DETAIL: Key (update_id)=(516) is not present in table "vci_updates".;
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> Error while executing the query" is returned when executing SQL statement "ALTER TABLE IF EXISTS vci_scanresults_targets ADD CONSTRAINT fk_vci_sresvms_ref_sig
2021-07-16T05:10:58.734Z INFO upgrade.states.component_states vcIntegrity:Import: --> FOREIGN KEY (update_id) REFERENCES vci_updates(id) ON DELETE CASCADE"

 

Environment

VMware vCenter Server 7.0.2

Cause

This issue occurs because some dangling records exist in the database of older releases and they need to be cleaned up before upgrading.

Resolution

To resolve this issue, upgrade to vCenter Server 7.0 U3 instead of vCenter Server 7.0 U2

Workaround:

If you cannot upgrade to 7.0 U3 from 7.0 U2, you can download and execute the attached remove-dangling-records.sh script on your older (source) VC and then upgrade again.

ssh root@<your-older-VC-IP-address-or-FQDN>
cd root
<download remove-dangling-records.sh to current folder>
chmod a+x remove-dangling-records.sh
./remove-dangling-records.sh