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: 2

The following stack trace specifically appears following the logs above:

1 com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername

For 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:

user_key

username

lower_username

402881a340e4a73a0140e4a7e42c0009

user1

user1

402881a340e4a73a0140e4a7e42c0008

user1

user1

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.

Updated on April 8, 2025

Still need help?

The Atlassian Community is here for you.