Synchronization with external directory fails with error: query did not return unique result due to duplicate groups
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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
1
2
3
4
5
6
7
8
9
10
11
12
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
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.1SELECT lower_group_name FROM cwd_group WHERE directory_id='<DIRECTORY_ID>' GROUP BY lower_group_name HAVING (COUNT(lower_group_name) > 1);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_idcolumn to verify that the duplicate groups reside in the same directory.1SELECT 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.
Solution
Resolution
Solution 1
Check for and remove duplicate groups from the external directory
Check the directory from the error for the duplicate groups identified by the diagnostic query.
Remove the duplicates from your external directory
If you're using LDAP you can also configure your Group Object Filter in the Group Schema Settings
If you're not sure which directory it is you can run the following query replacing
<DIRECTORY_ID>with the directory mentioned in the error1SELECT id,directory_name,directory_type,active FROM cwd_directory WHERE id=<DIRECTORY_ID>;After removing the duplicate groups from the external directory, or if adjusting the Group Object Filter has not resolved the issue:
Disable your external directory
Recreated your external directory with the same configuration settings and synchronize the directory
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.
Run the below query replacing the
<GROUP_NAME>with the duplicates displayed from the diagnostic query.1SELECT * FROM cwd_group WHERE lower_group_name="<GROUP_NAME>";Check the results and delete the newer group replacing
<id_of_duplicated_group>with theidvalue.Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
1DELETE FROM cwd_group WHERE id='<id_of_duplicated_group>';Empty the Confluence Cache
Admin > Cache Management > Flush all
Run a full directory synchronization
Was this helpful?