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

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

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.

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.

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

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-36018 - Duplicates in the People Directory due to duplicates in the user_mapping table. Follow the Workaround described in the ticket.