14 Replies Latest reply on Dec 14, 2017 8:08 PM by sathyay2k

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

    vMan Enthusiast

      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)

        • 1. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
          Enthusiast

          Thanks for sharing the workaround which you came up with!

          • 2. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
            haimg Lurker

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

            • 3. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
              vMan Enthusiast

              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

              • 4. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                vMan Enthusiast

                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?

                • 5. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                  antonkaiser Lurker

                  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!

                  • 6. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                    SanderSteffann Lurker

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

                    • 7. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                      danieldoulbeau Lurker

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

                      • 11. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                        sebasb Lurker

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

                        • 12. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                          vMan Enthusiast

                          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

                          • 13. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                            jrwise Novice

                            I just experienced this today with VCSA 6.5 Update 1.

                            • 14. Re: vCenter 6.5 - duplicate key value violates unique constraint “pk_vpx_vm_virtual_device”
                              sathyay2k Novice

                              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’;