UPDATE 11/03/2017: This issue has been patched with vCenter 6.5b, just search for “pk_vpx_vm_virtual_device“. and you will find the details. If you face this issue in PROD and are not willing to try the fix below just contact VMware Support for help.

They suggest the work around… 

Workaround from VMware: Unregister the problematic virtual machine from the host inventory and restart the vCenter Server. You might need to repeat this process if the failure happens again. (which is fine if you know which VM is causing the issue but this is not obvious from the logs / errors without going into the DB – IMO)

UPDATE 11/03/2017: This issue will be patched with the next vCenter patch I was told. I do not believe there will be a KB article published fot this issue as they don’t typically recommend users touch the DB.

UPDATE 16/12/2016: I reached out to VMWARE and was told they are aware of it and that they are working on a fix.

I will link to a KB once it have more info…

..

….

…..

I am not sure if I have stumbled onto a new vSphere 6.5 bug… but hopefully this post will help someone solve the same / similar type of unique constraint error I was getting on vSphere vCenter 6.5 which was crashing / stopping my vCenter.

I had a host fail and take down my vCenter and the AD DC/Gateway server for my lab, for some reason one the VM’s ended up generating a unique constraint error which caused my VC to crash and stop everytime I navigated to the configuration of the problem Virtual Machine.


 

DISCLAIMER

This method is most likely unsupported and could void your support from VMWARE should you cause any damage to the vRops Postgres DB…

This procedure modifies the vCenter database. Ensure to take a backup of the database before proceeding.

Perform it at your own risk!


 

The error(s):

An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) “ODBC error: (23505) – ERROR: duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”;

Error while executing the query” is returned when executing SQL statement “INSERT INTO VPX_VM_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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)”

An unrecoverable problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) “ODBC error: (23505) – ERROR: duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”;

Error while executing the query” is returned when executing SQL statement “INSERT INTO VPX_VM_VIRTUAL_DEVICE (ID, DEVICE_KEY, DEVICE_INFO_SUMMARY, DEVICE_INFO_LABEL, DEVICE_CONTROLLER_KEY, DEVICE_UNIT_NUMBER, DEVICE_TYPE, DEVICE_TEXT) VALUES (?, ?, ?, ?, ?, ?, ?, ?)”

uniqueconstrainterror1

!!!THE FIX!!!

1). Open and modify pg_hba.conf which allows me to connect to the DB without username and password from the localhost to perform the fix, updated the values from md5 to trust and save.

ph_hba-vcenter

2). Start the vCenter Services and wait for the DB to come online.

3). So now in the log file G:\VMwareDATA\vCenterServer\logs\vpostgres\postgresql*.log I am looking for the specific object which is generating the duplicate key value violates unique constraint so that i can build the select and delete statements later on.

Below you can see the ID’s which are generating the error (Id = 501 and device_Key = 16000) and (Id = 501 and device_Key = 0) as I actually had 2 devices which were related causing the unique constraint

2016-11-26 10:32:31.404 AEDT 5838c272.1698 149507 VCDB vc ERROR:  duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”

2016-11-26 10:32:31.404 AEDT 5838c272.1698 149507 VCDB vc DETAIL:  Key (id, device_key)=(501, 16000) already exists.

2016-11-26 10:32:31.404 AEDT 5838c272.1698 149507 VCDB vc STATEMENT:  INSERT INTO VPX_VM_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 ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

2016-11-26 10:33:16.022 AEDT 5838c18f.14e0 0   LOG:  Updating instance status…

2016-11-26 10:33:16.022 AEDT 5838c18f.14e0 0   LOG:  Writing instance status…

2016-11-26 10:33:16.022 AEDT 5838c18f.14e0 0   LOG:  Wrote instance status successfully.

2016-11-26 10:33:16.022 AEDT 5838c18f.14e0 0   LOG:  Updated instance status successfully.

2016-11-26 10:33:20.283 AEDT 5838c272.1698 149799 VCDB vc ERROR:  duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”

2016-11-26 10:33:20.283 AEDT 5838c272.1698 149799 VCDB vc DETAIL:  Key (id, device_key)=(501, 0) already exists.

2016-11-26 10:33:20.283 AEDT 5838c272.1698 149799 VCDB vc STATEMENT:  INSERT INTO VPX_VM_VIRTUAL_DEVICE (ID, DEVICE_KEY, DEVICE_INFO_SUMMARY, DEVICE_INFO_LABEL, DEVICE_CONTROLLER_KEY, DEVICE_UNIT_NUMBER, DEVICE_TYPE, DEVICE_TEXT) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)

2016-11-26 10:33:21.275 AEDT 5838c272.1694 0 VCDB vc LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

vcentre-postgres-errorlog-postgresssql-log

4). So now that I know the ID’s I want to target in the DB I will connect with pgadmin (example screenshots below are for vROPS but just leave the username and DB blank)

pgadmin-addserver

pgadmin-configserver

5). Navigate to the table “vpx_vm_virtual_device” in the VC database and lets do a select to see what device is causing the issue…

SELECT id, 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_key, device_type, device_text
	FROM vc.vpx_vm_virtual_device where id = '501';

Below we can see all the devices listed that belong to the “problem” VM, the specific ones causing me the issue are for a Wireless NetGear Card which I attached as a “Host USB Device

pgadmin-vcenter1

6). So to clean up the devices causing the issue and to allow vCenter to start without crashing… I will delete the 2 rows in this table generating the unique constraint error.

DELETE FROM vc.vpx_vm_virtual_device where id = '501' and device_key = '16000';
DELETE FROM vc.vpx_vm_virtual_device where id = '501' and device_key = '0';

pgadmin-vcenter-delete

7). I do one last select to make sure the devices are gone… and as you can see from the screenshot below the problem records are gone.

SELECT id, 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_key, device_type, device_text
	FROM vc.vpx_vm_virtual_device where id = '501';

pgadmin-vcenter-select-clean

8). Open and modify pg_hba.conf update the values from trust to md5 and save.

9). Restart the vCenter services and navigate to the problem VM and the vCenter should no longer crash, I was even able to reattach the “Host USB Device” (YAY – vCenter rebuild averted)

vcenter-crash-device

vMan