SDDC Manager password operations are not allowed because of a duplicate transaction_id in the passwordmanager.password_operations

Products

• VMware SDDC Manager

Issue/Introduction

• All the password operations are failing from the GUI.
• SDDC manager contains errors similar to the excerpt below:
  
  /var/log/vmware/vcf/operationsmanager/operationsmanager.log
yyyy-mm-ddThh:mm:ss.Z ERROR [vcf_om,xxxxxxxxxxxxxxxx,xxxx] [c.v.v.p.r.PasswordManagerController,http-nio-127.0.0.1-7300-exec-5] Exception has occured in fetching password workflow details, invalid UUID: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, {}
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 2
        at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:385)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:257)

Resolution

IMPORTANT NOTE: Make sure that fresh backup/snapshots of the SDDC manager have been taken prior to any modification.

 

• Check the SDDC DB for duplicate workflow_id entries:
  
  psql -U postgres -h localhost -d operationsmanager -c "select transaction_id, workflow_id, operation_type, transaction_status from passwordmanager.password_operations where transaction_status !='SUCCEEDED' AND transaction_status !='USER_CANCELLED';"
  
  
• Remove the duplicate entries in the SDDC DB using the "transaction_id"as reference:
  
  psql -h localhost -U postgres -d operationsmanager -c "delete from passwordmanager.password_operations where transaction_id='XXXX';"

• Update the "FAILED" and "PREVALIDATION_FAILED" tasks to "USER_CANCELLED" to unblock the password operation workflow:
  
  psql -U postgres -h localhost -d operationsmanager -c "update passwordmanager.password_operations set transaction_status='USER_CANCELLED' where transaction_status !='SUCCEEDED' AND transaction_status !='USER_CANCELLED';"