Synchronization with external directory fails with error: query did not return unique result

Still need help?

The Atlassian Community is here for you.

Ask the community

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-36018 - Getting issue details... STATUS . Follow the Workaround described in the ticket.

Description

When doing a manual synchronization with an external directory, the synchronization fails and few users/groups are synchronized with Confluence. 

ProductConfluence
PlatformServer
Last modified on Mar 22, 2023

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.