Error: "query did not return a unique result" while creating anti-affinity rules or updating VMs in VCD

Products

• VMware Cloud Director
• VMware Telco Cloud Platform

Issue/Introduction

• When attempting to create anti-affinity rules or update virtual machines within VMware Cloud Director, the task fails abruptly.
• The user interface or system logs record the following error:

org.hibernate.NonUniqueResultException: query did not return a unique result: 2

• This error occurs because the underlying database contains multiple conflicting records where only a single, unique object record is expected.

Environment

VCD: 10.3, 10.4, 10.5, 10.6

TCP: 4.0, 4.0.1, 5.0, 5.0.1, 5.0.2, 5.1

Cause

• The issue is triggered by duplicate entries within the computevm database table.
• Specifically, a single VM managed object reference is incorrectly linked to two distinct IDs.
• This usually happens when a legacy record is left behind in an UNDEPLOYED state while a newer, active record exists for the exact same object.
• When VCD queries the database to apply changes or rules to the VM, the query returns both rows and fails.

Resolution

• To resolve this, you must identify and remove the redundant database entry.
• Back up the embedded database before proceeding with any manual deletions.
• Refer to the VCD Appliance Backup Guide for detailed steps.
• This must be completed before executing the steps below.

1. Establish an SSH session to the primary node as root and log into the VCD database:
   
   sudo -i -u postgres psql -d vcloud

2. Identify the specific object causing the conflict by reviewing the job details:
   
   SELECT * FROM jobs WHERE object = '[Object_Name]';
SELECT * FROM job_detail WHERE job_id = '[Job_UUID]';

3. Execute a query to isolate which VM MoRefs have more than one entry:
   
   SELECT id, vmmoref, vc_id, deployment_status
FROM (
    SELECT id, vmmoref, vc_id, deployment_status,
    COUNT(*) OVER (PARTITION BY vc_id, vmmoref) as count
    FROM computevm
) sub
WHERE count > 1
ORDER BY vmmoref;

4. Query the duplicate rows using the specific vmmoref found in the previous step (for example, vm-####):
   
   SELECT * FROM computevm WHERE vmmoref = 'vm-1234';

Review the output to target the stale record. The invalid row is typically marked as UNDEPLOYED while the valid active row is marked as DEPLOYED.

5. Delete the specific stale row using its unique ID:
   
   DELETE FROM computevm WHERE id = '[ID from step 3]';