VPXD crashes due to ERROR: duplicate key value violates unique constraint "pk_vpx_vm_sn_virtual_device"

Products

• VMware vCenter Server

Issue/Introduction

To resolve the issue related to the VCDB crash due to a duplicate value in the vCenter server database.

Symptoms -
VPXD crashes with a VCDB error

 

vpxd.log entries -

[YYYY-MM-DDTHH:MM:SS] error vpxd[42976] [Originator@6876 sub=Default opID=m3ymzqr5-21791-auto-gtc-h5:70007395-80] [VdbStatement] SQLError was thrown: "ODBC error: (23505) - ERROR: duplicate key value violates unique constraint "pk_vpx_vm_sn_virtual_device"
--> DETAIL: Key (id, device_key)=(1360722, 200) already exists.;
--> Error while executing the query" is returned when executing SQL statement "INSERT INTO VPX_VM_SN_VIRTUAL_DEVICE (ID,DEVICE_KEY,CONN_AL_GU_CONTROL_FLAG,CONN_CONNECTED_FLAG,CONN_START_CONNECTED_FLAG,CONN_STATUS,DEVICE_INFO_SUMMARY,DEVICE_INFO_LABEL,DEVICE_CONTROLLER_KEY,DEVICE_UNIT_NUMBER,DEVICE_TYPE,DEVICE_TEXT) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"

Environment

• vCenter Server 7.0
• vCenter Server 8.0

Cause

A duplicate value in the VCDB causes the VPXD service to crash.

It is highly recommended to set the DRS on the cluster to 'manual' while performing a vCenter server patch/ upgrade activity to avoid such an issue.

Resolution

Before you begin introducing any changes on the vCenter server database; Please ensure to keep offline snapshots / valid VCDB backup of the database.

Refer KB - Back up and restore vCenter Server Appliance/vCenter Server 6.x 7.0.x 8.0.x vPostgres database

 

After identifying the impacted Key ID value from the vpxd logs. We need to follow the below steps to resolve this issue.

1. SSH to the vCenter server via root.

Stop the VPXD service, if the service has not yet crashed in your context.

# service-control --stop vpxd

 

2. Connect to the VCDB:

# /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres

 

3. Run the following command to identify the VM ID associated with the KEY ID found in the vpxd.log:  (good to know information only)

VCDB=# select id, file_name, dns_name from vpx_vm where id=1360722;
   id    |                                                          file_name                                                           |   dns_name
---------+------------------------------------------------------------------------------------------------------------------------------+--------------
 1360722 | ds:///vmfs/volumes/Datastore-UUID/vCLS-46a626ff-e554-4018-8ad1-5858e1ceb958/vCLS-46a626ff-e554-4018-8ad1-5858e1ceb958.vmx    | photon3-hdcs
(1 row)

 

4. Count the number of duplicate entries that exist in the table (good to know info only).. associated with the KEY ID we have observed from the vpxd.log

VCDB=# select id from vpx_vm_sn_virtual_device where id=1360722;
   id
---------
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
 1360722
(15 rows)

 

5. Remove all duplicate entries from the table to resolve this issue.. associated with the KEY ID we have observed from the vpxd.log

VCDB=# DELETE FROM vpx_vm_sn_virtual_device WHERE id=1360722;
DELETE 15

6. Disconnect from the VCDB

VCDB=# \q 

7. Start the VPXD service

# service-control --start vpxd

 

Note: If the above command fails to start vpxd, restart all services instead

# service-control --stop --all; service-control --start --all

Monitor the start of the VPXD service or all services in a separate SSH instance.

# watch service-control --status all