Synchronization with external directory fails with error: query did not return unique result
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 with an external directory, the synchronization fails and few users/groups are synchronized with Confluence.
The following error appears in the UI, and in the atlassian-confluence.log:
1
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2The following stack trace specifically appears following the logs above:
1
com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsernameFor example:
1
2
3
4
5
6
7
2015-06-23 12:56:20,326 ERROR [scheduler_Worker-5] [atlassian.crowd.directory.DbCachingDirectoryPoller] pollChanges Error occurred while refreshing the cache for directory [ 7864321 ].
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.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername(HibernateConfluenceUserDao.java:82)If you're not seeing the exact string HibernateConfluenceUserDao.findByUsername, refer to User related issues in Confluence with the error message 'query did not return a unique result'.
Diagnosis
Run the 1st Diagnosis to see if you are affected. Only run the 2nd Diagnosis should the 1st Diagnosis returns no results.
Diagnosis 1 - Duplicated non-null records in the user_mapping table
Run the queries below to find duplicate users in the user_mapping table:
1
SELECT * FROM USER_MAPPING WHERE LOWER_USERNAME IN (SELECT LOWER_USERNAME FROM USER_MAPPING GROUP BY LOWER_USERNAME HAVING COUNT(*) > 1);If these queries returns any result, proceed with Resolution 1.
If these queries returns no result, proceed with Diagnosis 2.
Diagnosis 2 - Null records in the user_mapping table:
Run the query below to find NULL records in the user_mapping table:
1
SELECT * FROM USER_MAPPING WHERE USERNAME IN (SELECT USERNAME FROM USER_MAPPING WHERE LOWER_USERNAME IS NULL);If this query returns any result, proceed with Resolution 2.
Cause
This error is caused by a duplicate value in the database or due to some corruption in the directory cache.
Solution
Resolution
Resolution 1 - Duplicated records in the user_mapping table
Step 1: Review user_key values of duplicated users
If the Diagnosis 1 query returns records, they'll look something like this:
|
|
|
|---|---|---|
|
|
|
|
|
|
Step 2: Verify whether content is associated with user_key
From the obtained user_keys, verify which duplicated user to delete by filtering out which user_key doesn't have created content. Returned count should be 0.
1
2
3
4
5
6
7
SELECT UM.USER_KEY FROM USER_MAPPING AS UM, CONTENT AS C
WHERE
(UM.USER_KEY = C.CREATOR OR
UM.USER_KEY = C.LASTMODIFIER)
AND C.CONTENTTYPE != 'USERINFO'
AND UM.USER_KEY IN ('<obtained_user_key1>','<obtained_user_key2>')
GROUP BY UM.USER_KEY;Step 3: Delete user_key that isn't associated with any content
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.
Delete the users that had count of 0 from above query. Only delete duplicate entries and leave one exist presumably with a result other than 0.
1
2
DELETE FROM CONTENT WHERE CONTENTTYPE = 'USERINFO' and USERNAME = '<duplicated_user_key>';
DELETE FROM USER_MAPPING WHERE USER_KEY= '<duplicated_user_key>';Resolution 2 - NULL records in the user_mapping table
You are affected by the bug reported here: CONFSERVER-36018 - Duplicates in the People Directory due to duplicates in the user_mapping table. Follow the Workaround described in the ticket.
Was this helpful?