ERROR: duplicate key value violates unique constraint "pk_api_externalservice" while upgrading VMware Cloud Director

Products

• VMware Cloud Director

Issue/Introduction

• When running upgrade utility to upgrade VMware Cloud Director database to 10.6.x, it fails with error. 

^{Error during Upgrade Vcloud Director to 10.6.x - ERROR:  duplicate key value violates unique constraint "pk_api_externalservice"}

• postgresql.log under  /var/vmware/vpostgres/current/pgdata/log/ includes errors as below : 

  ^{ERROR:  duplicate key value violates unique constraint "pk_api_externalservice"
  DETAIL:  Key (id)=(########-####-####-########) already exists.
  STATEMENT:}

                      -- Update current API External Service IDs to UUID5
                    UPDATE api_external_service SET id = tm_uuid_generate_v5('com.vmware.vcloud.constants.extension',
                        vendor || '.' || name || ':' || version)
                        WHERE external_system_type='SERVICE'
LOG:  could not receive data from client: Connection reset by peer

• vcloud-container-debug.log under /opt/vmware/vcloud-director/logs/ includes errors as below : 

^{WARN     | pool-1-thread-1           | SerialAggregateTask            | Update existing RDE IDs to UUID5: Task failed due to uncaught exception |
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_api_externalservice"
  Detail: Key (id)=(########-####-####-########) already exists.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312)
        at jdk.internal.reflect.GeneratedMethodAccessor232.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:566)
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
        at com.sun.proxy.$Proxy35.execute(Unknown Source)
        at com.vmware.upgrade.sql.task.RawSQLTask.call(RawSQLTask.java:125)
        at com.vmware.upgrade.task.SerialAggregateTask.call(SerialAggregateTask.java:65)
        at com.vmware.upgrade.task.AbstractDelegatingTask.doCall(AbstractDelegatingTask.java:144)
        at com.vmware.upgrade.sql.task.TransactionTask.doCall(TransactionTask.java:95)
        at com.vmware.upgrade.task.AbstractDelegatingTask.call(AbstractDelegatingTask.java:123)
        at com.vmware.upgrade.task.SerialAggregateTask.call(SerialAggregateTask.java:65)
        at com.vmware.upgrade.task.AbstractDelegatingTask.doCall(AbstractDelegatingTask.java:144)
        at com.vmware.upgrade.factory.GraphUpgradeDefinitionFactory$UpgradeTask.doCall(GraphUpgradeDefinitionFactory.java:127)
        at com.vmware.upgrade.task.AbstractDelegatingTask.call(AbstractDelegatingTask.java:123)
        at com.vmware.upgrade.task.SerialAggregateTask.call(SerialAggregateTask.java:65)}

 

 

Environment

VMware Cloud Director 10.6.x

Cause

This occurs as the upgrade script tries to update the UUIDs using "tm_uuid_generate_v5" and fails due to a duplicate key in the "api_external_service" table. The ID in the error may not exist pre and post upgrade attempt as it tries to generate same UUID for multiple entries that has same name in the "api_external_service" table.

Resolution

The name in api_external_service table must be kept unique or clean up the legacy entry for the duplicate service.

It can be done by using API.

1. GET /admin/extension/service/query (To get the above list of external services created)
   https://developer.broadcom.com/xapis/vmware-cloud-director-api/latest/doc/operations/GET-ServicesFromQuery.html
2.  PUT /admin/extension/service/{id} (Alter the name to be unique)
   https://developer.broadcom.com/xapis/vmware-cloud-director-api/latest/doc/operations/PUT-Service.html
3. Guide to API extensibility: 
   https://techdocs.broadcom.com/us/en/vmware-cis/cloud-director/vmware-cloud-director/10-6/-vcloud-api-programming-guide-for-service-providers-10-6/extending-vcloud-director-api/vcd-extension-services-api/register-an-extension-service-api.html

For a workaround, contact Broadcom Technical Support and note this Knowledge Article ID (402926) in the problem description.

For more information, see How to Submit a Support Request