vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”

[carvaled]

Hello All,

Just wanted to share an issue I had and get feedback if anyone has experienced this... new bug?

My vCenter 6.5 server was running on a host which experienced a failure... I manually recovered the failed VM's onto another host, once all VM's were back online the vCenter started to crash as soon as I tried to view the properties of a specific VM (via the Webclient) which was also on that original failed host.

The second the properties loaded in the webgui, vCenter would shutdown with the following error:

"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"

I asked DR google and couldn't really find anything specific to “pk_vpx_vm_virtual_device”... has anyone experienced this one before on other versions?

After some investigation I linked this back to the “Host USB Device” and I managed to solve my problem. As I couldn't find anything relevant to this issue I thought I would do a simple write up about it for anyone is interested / experiences the problem.

Cheers

vMan

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. (vMAN: 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)

[admin]

Thanks for sharing the workaround which you came up with!

[haimg]

This is definitely a new one for 6.5... I've seen the issue several times already, unfortunately, and it's not limited exclusively to USB devices. I think the issue happens if the VM config was modified in some way while vCenter was down. It seems that even just starting a VM while vCenter is down could trigger it.

I've managed to recover from the issue in a much more simple way, but my solution is much more limited.

1. Bring the problematic VM down (I knew which one it is because of a specific device mentioned in the log output, but otherwise it could be a problem).

2. Unregister the VM on the host.

3. Bring the vpxd service back up.

4. Unregister the now "orphaned" VM on vCenter.

5. Re-register the VM on the host, and start it again.

This seems like a serious issue with vCenter, and there is no easy or simple way to recover. I'd hate to tinker with vCenter SQL database directly, and my "solution" requires to shut down and unregister the VM, and there is no easy way to know which VM is causing problems...

[carvaled]

Thanks for the feedback, I am glad it was not just me... (even tho its a serious one...)

I guess this is a new bug which we need to make VMware aware of...

Cheers

vMan

[carvaled]

thanks, looks like I'm not the only one experiencing this issue.

I see you work for VMware, do you know if this is a known issue / reported bug?

[antonkaiser]

For me this happened during tests with the vCenter Appliance 6.5 after I tried to migrate a VMkernel port for management traffic from a vSwitch to a vDS on ESXi 6U2. It was the same VMkernel that was handling the connection with vCenter (and that host was also running the vCenter VM), and that also resulted in a few other issues: looking directly at the host (using the vSphere C# client) via a secondary VMkernel, nothing seemed to have changed, but still ESX wasn't responding to API calls on that port and forced me to reboot the host after trying to get that VMkernel back up. Anyways, after a few modifications on the host I ran into the duplicate key issue (see below for extracts from the log).

Wasn't able to remove the host from the cluster in vCenter Web Client, so I restarted vCenter Server and tried with the HTML5 ui, which, to my surprise, worked. Still, I couldn't add the host again (a network timeout according to the GUI after entering credentials; ping from the vCenter appliance worked, connecting to that VMkernel port from the C# client worked), so I deleted the cluster and also tried deleting the data center (no luck with that). Another reboot of the host solved this and I was able to add it to vCenter, and no more issues so far.

Summary:

1. Shut down the host that is responsible (or change the IP/DNS of its VMkernel so vCenter can't find it).
2. Remove the host from vCenter. Reboot vCenter (and the host if you couldn't kill the connection) and try the HTML5 ui if you don't succeed on the 1st try.
3. (maybe optional) Reboot the host
4. Add the host back to vCenter

Hope that helps anyone who doesn't want to dig into the database. I was ready to do it on the server directly, for which I found the instructions here quite helpful to issue SQL commands directly inside the vCenter Appliance VM: Reviewing and Managing Local and Global Permissions in vCenter Server 6.0 (2123931) | VMware KB

] Bind parameters:

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [0]datatype: 1, size: 4, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = 30

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [1]datatype: 1, size: 4, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = 1000

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [2]datatype: 11, size: 26, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = "LSI Logic SAS"

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [3]datatype: 11, size: 34, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = "SCSI controller 0"

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [4]datatype: 1, size: 4, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = 100

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [5]datatype: 1, size: 4, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = 3

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [6]datatype: 11, size: 84, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = "vim.vm.device.VirtualLsiLogicSASController"

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] [7]datatype: 11, size: 1188, arraySize: 0

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [VdbStatement                                                     ] value = "<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xml                                                     ns="urn:vim25" versionId="6.5" xsi:type="VirtualLsiLogicSASController"><key>1000</key><device..."

2017-01-08T02:52:01.334Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] [Vdb::IsRecov                                                     erableErrorCode] Unable to recover from 23505:7

[...]

SQLError was thrown: "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_TEX                                                     T) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

2017-01-08T02:52:01.343Z error vpxd[7F101ADDB700] [Originator@6876 sub=vpxCommon opID=QS-host-15-5916c471] [Vpxd_Handl                                                     eVmRootError] Received unrecoverable VmRootError. Generating minidump ...

2017-01-08T02:52:01.343Z error vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471] An unrecovera                                                     ble problem has occurred, stopping the VMware VirtualCenter service. Error: Error[VdbODBCError] (-1) "ODBC error: (235                                                     05) - 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_TEX                                                     T) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"

2017-01-08T02:52:01.348Z panic vpxd[7F101ADDB700] [Originator@6876 sub=Default opID=QS-host-15-5916c471]

-->

--> Panic: Unrecoverable VmRootError. Panic!

[SanderSteffann]

The same just happened to me with a virtual CD drive. Deleting the "duplicate" record from the database solved it. Thanks for the writeup!

[danieldoulbeau]

Thanks very much, you saved my life with this solution !

[carvaled]

glad it was helpful

[JB2015]

How did you remove the duplicated key?

[carvaled]

follow the instructions here http://vman.ch/vcenter-vdbodbcerror-unique-constraint-pk_vpx_vm_virtual_device-fix/​  (at your own risk)

Cheers

Edgar

[sebasb]

Thank you so much, 1st result from google your workaround!

[carvaled]

glad it helped, apparently they fixed it in 6.5b but i was also made aware that it appears to still be occurring for some users after the patch.

VMware vSphere 6.5.0b Release Notes

[jrwise]

I just experienced this today with VCSA 6.5 Update 1.

[sathyay2k]

vpostgres.log

2017-12-15 01:34:30.751 UTC 5a332683.1759 7491543 VCDB vc ERROR:  duplicate key value violates unique constraint "pk_vpx_vm_virtual_device"

2017-12-15 01:34:30.751 UTC 5a332683.1759 7491543 VCDB vc DETAIL:  Key (id, device_key)=(260, 8000) already exists.

2017-12-15 01:34:30.751 UTC 5a332683.1759 7491543 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_IN

FO_LABEL, DEVICE_CONTROLLER_KEY, DEVICE_UNIT_NUMBER, DEVICE_TYPE, DEVICE_TEXT) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)

2017-12-15 01:34:42.862 UTC 5a332683.1759 7491554 VCDB vc LOG:  could not receive data from client: Connection reset by peer

2017-12-15 01:34:42.863 UTC 5a332683.1759 7491554 VCDB vc LOG:  unexpected EOF on client connection with an open transaction

2017-12-15 01:35:13.349 UTC 5a3325e0.acf 0   LOG:  Updating instance status...

Resolution

DELETE FROM vpx_vm_virtual_device where id=’260’ and device_key=’8000’;