"insert or update on table" error encountered when performing the database upgrade for Cloud Director 10.3.1

Products

• VMware Cloud Director

Issue/Introduction

Symptoms:

• Error "insert or update on table" encountered during the database upgrade
  • insert or update on table "vm_container" violates foreign key constraint "fk_vm_container2org_member"
  • insert or update on table "vapp_vm" violates foreign key constraint "fk_vapp_vm2vm"

 

Environment

VMware Cloud Director 10.x

Cause

This issue occurs due to stale information present in tables associated with a foreign key constraint.
For the two tables involved, data will be missing in one table which causes a conflict with said key constraint.

Resolution

This is a Known Issue affecting Cloud Director 10.3.1.

The two possible errors are resolved in the following Cloud Director releases:

Error 1:
insert or update on table "vm_container" violates foreign key constraint "fk_vm_container2org_member"

Resolution:
This issue is resolved in the 10.3.3.2 release of Cloud Director and later.


Error 2:
insert or update on table "vapp_vm" violates foreign key constraint "fk_vapp_vm2vm"

Resolution:
This issue is resolved in the 10.4 release of Cloud Director and later.

Workaround:
Depending on the error encountered, you will need to run a particular query to identify to stale data.

Error 1:
insert or update on table "vm_container" violates foreign key constraint "fk_vm_container2org_member"

Query 1:
select distinct vm_container.sg_id, name, user_id, org_id from vm_container where 
vm_container.user_id  not in (select org_member.member_id from org_member);


Error 2:
insert or update on table "vapp_vm" violates foreign key constraint "fk_vapp_vm2vm"

Query 2:
select * from vapp_vm where svm_id not in (select id from vm);


Once identified, please open a Support Request for assistance with the update/removal of stale information.