vCloud Usage Meter collection fails with the error: duplicate key value violates unique constraint

Products

• VMware Aria Suite

Issue/Introduction

Symptoms:

• vCloud Usage Meter collection fails
• Collection fails with a duplicate key constraint error
• In the UM log file, you see entries similar to:
  
  2023-04-17 16:40:08,295 ERROR [pool-3486-thread-2] collect.Collector$: A collection from example.com, begun at 2023-04-17 16:40:04, failed at 2023-04-17 16:40:08. java.lang.RuntimeException: Exception while executing statement : ERROR: duplicate key value violates unique constraint "idx5802080f" errorCode: 0, sqlState: xxxxx insert into "Host" ("id", "licenseId", "uuid", "moref", "dnsName", "vcServerId", "memSize") values (nextval('"s_Host_id"'),?,?,?,?,?,?) jdbcParams:[x,<UUID_1>,host-<UUID_2>,example.com,x,xxxxxxxx]
  
  Where idx5802080f is a unique key constraint for the combination of vcServerId and moref for hosts
  
  This log extract includes these details:
   
  • The ID – (Unique key specific to the DB)
  • The UUID – <UUID_1>
  • The Moref host – <UUID_2> (DB reference)
     
• If you run a select statement on the database for this Moref, you see that it has a different UUID
  
  For example, when you run this command:
   
  usage_meter_db=# select * from "Host" where moref = 'host-<UUID_2>';
   
  You see an output similar to:
   
  id | uuid | vcServerId | moref | dnsName | licenseId | memSize
  --------+--------------------------------------+------------+-----------+-----------------------------------+
  xx | <UUID_3> | x | host-<UUID_2> | <HOSTNAME> | x | xxxxxxxx
   
  In this output, you can see that the UUIDs are different:
   
  <UUID_3> (UUID Present in the DB)
   
  <UUID_1> (UUID being inserted)

Environment

VMware vCloud Usage Meter 3.x

Cause

This issue occurs when the UUID on the host has changed.

Resolution

Note: Ensure to amend the statement to suit your environment