Content Library sync or delete fails with RuntimeFault.summary and NonUniqueResultException

Products

• VMware vCenter Server 8.0
• VMware vCenter Server 7.0

Issue/Introduction

• Sync or delete Content Library failed with "RuntimeFault.summary" 
  
• When trying to deploy a new virtual machine using a template in the Content Library, the process failed with "Unable to process template."
• In /var/log/vmware/content-library/cls.log on the vCenter server, the following entry is observed:
  YYYY-MM-DDTHH:MM:SS.054Z | DEBUG    | ml9rbtxy-577498-auto-cdln-h5:70079657-6b-14 | cls-simple-activity-11    | SimpleActivityExecutor         | submitting activity DeleteLibraryItemActivity (priority=NORMAL, phase=phaseBegin()
YYYY-MM-DDTHH:MM:SS.069Z | INFO     | ml9rbtxy-577498-auto-cdln-h5:70079657-6b-14-83 | cls-simple-activity-21    | DeleteLibraryItemActivity      | Deleting library item 49d876fa-####-####-8fb7-599db6fdf26d
YYYY-MM-DDTHH:MM:SS.069Z | DEBUG    | ml9rbtxy-577498-auto-cdln-h5:70079657-6b-14-83 | cls-simple-activity-21    | TypeAdapterServiceImpl         | Deleting type specific content of library item 49d876fa-####-####-8fb7-599db6fdf26d
YYYY-MM-DDTHH:MM:SS.073Z | ERROR    | ml9rbtxy-577498-auto-cdln-h5:70079657-6b-14-83 | cls-simple-activity-21    | SimpleActivityWorker           | Exception will not be handled by activity DeleteLibraryItemActivity (handle=b6905707-####-####-98d4-2277a22450c0, entityId=49d876fa-####-####-8fb7-599db6fdf26d): query did not return a unique result: 2; nested exception is javax.persistence.NonUniqueResultException: query did not return a unique result: 2
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) ~[spring-orm-5.3.36.jar:5.3.36]
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:552) ~[spring-orm-5.3.36.jar:5.3.36]
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61) ~[spring-tx-5.3.36.jar:5.3.36]
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:243) ~[spring-tx-5.3.36.jar:5.3.36]
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152) ~[spring-tx-5.3.36.jar:5.3.36]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.36.jar:5.3.36]
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145) ~[spring-data-jpa-2.7.17.jar:2.7.17]
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.3.36.jar:5.3.36]
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97) ~[spring-aop-5.3.36.jar:5.3.36]

Environment

• vCenter Server 7.x
• vCenter Server 8.x

Cause

Duplicate entries for one or more content library items in vCenter's Database.

Resolution

The issue can be identified and workaround by following the steps below

1. Log in to vCenter via ssh with the root account
2. Log in to VCDB with the following command:
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
3. Check Content Library certificate items with the following command
   SELECT * FROM cl_libraryitem_certificate_verification;

Example: The screenshot below shows there are 3 pairs of the same item ID, which means there are 3 duplicate entries in the database.

To workaround this issue, need to delete the duplicate entries in the Content Library certificate database by following the steps below.

Note: Before applying the steps below, take a backup or a snapshot of the vCenter Server Appliance. If the vCenter is part of an Enhanced Linked Mode setup, take an offline snapshot of all the vCenters in Enhanced Linked Mode(ELM). VMware vCenter in Enhanced Linked Mode pre-changes snapshot (online or offline) best practice

1. Log in to vCenter via ssh with the root credentials
2. Stop the content library and vpxd services using the commands below
   service-control --stop vmware-content-library
service-control --stop vpxd
3. Log in to VCDB with the following command:
   /opt/vmware/vpostgres/current/bin/psql -d VCDB -U postgres
4. Delete duplicate entries in the database using the following command:
   DELETE FROM cl_libraryitem_certificate_verification cert_ver_old USING (SELECT MIN(ctid) as ctid, itemId FROM cl_libraryitem_certificate_verification GROUP BY itemId HAVING COUNT(*) > 1) cert_ver_new WHERE cert_ver_new.itemId = cert_ver_old.itemId AND cert_ver_new.ctid <> cert_ver_old.ctid;
5. Verify that the duplicate entry has been successfully deleted by executing the command 
   SELECT * FROM cl_libraryitem_certificate_verification;
6. Quit the VCDB using the below command
   \q
7. Start the content library and vpxd services using the commands below
   service-control --start vmware-content-library
service-control --start vpxd
8. Re-sync the OVF template within the content library.