Problem

When doing a manual synchronization or attempting to log in with an external directory, the synchronization fails and few users/groups are synchronized with Confluence.

The following appears in the atlassian-confluence.log

2016-06-13 19:14:43,396 ERROR [scheduler_Worker-5] [atlassian.crowd.directory.DbCachingDirectoryPoller] pollChanges Error occurred while refreshing the cache for directory [ 23625729 ].
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2
	at org.springframework.orm.hibernate.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:590)
	at org.springframework.orm.hibernate.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:353)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:375)
	at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:337)
	at com.atlassian.crowd.embedded.hibernate2.HibernateGroupDao.internalFindByName(HibernateGroupDao.java:359)
	at com.atlassian.crowd.embedded.hibernate2.HibernateGroupDao.findByName(HibernateGroupDao.java:51)
	at com.atlassian.confluence.user.crowd.CachedCrowdGroupDao.lambda$getGroupCache$539(CachedCrowdGroupDao.java:60)
	at com.atlassian.confluence.cache.option.OptionalReadThroughCache.get(OptionalReadThroughCache.java:35)
	at com.atlassian.confluence.user.crowd.CachedCrowdGroupDao.findGroup(CachedCrowdGroupDao.java:88)
	at com.atlassian.confluence.user.crowd.CachedCrowdGroupDao.findByNameWithAttributes(CachedCrowdGroupDao.java:118)

Diagnosis

1. Run the query below to find duplicate groups in the directory mentioned in the error. Replace <DIRECTORY_ID> with the directory mentioned in the error.

   SELECT lower_group_name FROM cwd_group WHERE directory_id='<DIRECTORY_ID>' GROUP BY lower_group_name HAVING (COUNT(lower_group_name) > 1);

2. If there is no directory ID mentioned in the error, you can run the below query to display any duplicate group names. Then check the directory_id column to verify that the duplicate groups reside in the same directory.

   SELECT lower_group_name FROM cwd_group GROUP BY lower_group_name HAVING (COUNT(lower_group_name) > 1);

Cause

This error is caused by: a duplicate value in the database, some corruption in the directory cache, or duplicates in the LDAP.

Resolution

Solution 1

Check for and remove duplicate groups from the external directory

1. Check the directory from the error for the duplicate groups identified by the diagnostic query.
   1. Remove the duplicates from your external directory
   2. If you're using LDAP you can also configure your Group Object Filter in the Group Schema Settings

2. If you're not sure which directory it is you can run the following query replacing <DIRECTORY_ID> with the directory mentioned in the error

    SELECT id,directory_name,directory_type,active FROM cwd_directory WHERE id=<DIRECTORY_ID>;

3. After removing the duplicate groups from the external directory, or if adjusting the Group Object Filter has not resolved the issue:

   1. Disable your external directory

   2. Recreated your external directory with the same configuration settings and synchronize the directory
   3. After a successful full synchronization, verify that users are able to log in and see the same content

Solution 2

Remove duplicate groups from the database

If you don't find any duplicates in your external directory, remove the duplicates directly from the cwd_group table.

1. Run the below query replacing the <GROUP_NAME> with the duplicates displayed from the diagnostic query.

    SELECT * FROM cwd_group WHERE lower_group_name="<GROUP_NAME>";

2. Check the results and delete the newer group replacing<id_of_duplicated_group> with the id value.

   Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

    DELETE FROM cwd_group WHERE id='<id_of_duplicated_group>';

3. Empty the Confluence Cache
   1. Admin > Cache Management > Flush all
4. Run a full directory synchronization