Synchronization with external directory fails with error: query did not return unique result
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
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:
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2
The following stack trace specifically appears following the logs above:
com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername
For example:
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:
SELECT lower_username FROM user_mapping WHERE lower_username IS NOT NULL GROUP BY lower_username HAVING (COUNT(lower_username) > 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:
SELECT * 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.
Resolution
Resolution 1 - Duplicated records in the user_mapping table
Step 1: Obtain user_key
of duplicated users
You can find the user_key
with the following query by using the lower_username
from the first query:
SELECT * FROM user_mapping WHERE lower_username = '<Result of Query From Diagnosis #1 above>';
The result should be as follow:
user_key | username | lower_username |
---|---|---|
402881a340e4a73a0140e4a7e42c0009 | user1 | user1 |
402881a340e4a73a0140e4a7e42c0008 | user1 | user1 |
Step 2: Verify no content 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.
SELECT COUNT(*) FROM CONTENT WHERE CREATOR = '<user_key>' AND CONTENTTYPE != 'USERINFO';
Step 3: Delete user
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 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.
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-36018Getting issue details... STATUS . Follow the Workaround described in the ticket.