Problem

The following issues are observed in Confluence:

• Users are unable to login to Confluence
• A specific user is unable to login to Confluence
  
• User Directory synchronization failed

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

Cause

This error is caused by a duplicate value in the database. This might be due to:

• Some corruption in the directory cache.
• Corruption in the database.
• Missing unique constraints in the database. Some versions of Confluence introduced missing unique constraints upon installation. This persists throughout Confluence upgrade. This issue is mentioned here and is fixed in Confluence 7.1   CONFSERVER-58261 - Getting issue details... STATUS  

Diagnosis 1 - Check which table is affected

To diagnose this issue, check the error message in the logs, and check if the stack trace that follows contains either the following:

1. com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUser
2. com.atlassian.confluence.user.persistence.dao.hibernate.HibernateConfluenceUserDao.findByUsername
3. com.atlassian.crowd.embedded.hibernate2.HibernateGroupDao.internalFindByName

Example - The following stack trace is shown when you are affected by the first issue:

2018-03-01 09:34:39,537 INFO [Caesium-1-4] [atlassian.crowd.directory.DbCachingRemoteDirectory] synchroniseCache failed synchronisation complete for directory [ 884738 ] in [ 101138ms ]
2018-03-01 09:34:39,600 ERROR [Caesium-1-4] [atlassian.crowd.directory.DbCachingDirectoryPoller] pollChanges Error occurred while refreshing the cache for directory [ 884738 ].
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2; nested exception is org.hibernate.NonUniqueResultException: query did not return a unique result: 2
	at org.springframework.orm.hibernate5.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:256)
	at org.springframework.orm.hibernate5.HibernateTemplate.doExecute(HibernateTemplate.java:362)
	at org.springframework.orm.hibernate5.HibernateTemplate.execute(HibernateTemplate.java:313)
	at com.atlassian.crowd.embedded.hibernate2.HibernateUserDao.internalFindUser(HibernateUserDao.java:541)

1. If the first stack trace is identified, this means that there are duplicated records in the cwd_user table. Proceed with the Diagnosis 2 below to find out which user is duplicated.
2. If the second stack trace was identified, this means that there are duplicated records in the user_mapping table. Proceed to the following page:

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

3. If the third stack trace was identified, this means that there are duplicated records in the cwd_group table. Proceed to the following page:

   1. Synchronization with external directory fails with error: query did not return unique result due to duplicate groups

Diagnosis 2 - Find duplicated users in the cwd_user table

Diagnosis Query - 1: Find duplicate users in the cwd_user table that has the same External ID and belong to the same directory:

SELECT directory_id, external_id
              FROM cwd_user
              GROUP BY directory_id, external_id
              HAVING ( COUNT(external_id) > 1)

Diagnosis Query - 2: Find duplicate users in the cwd_user table that has the same lower_user_name and belong to the same directory:

SELECT lower_user_name, directory_id
		from cwd_user 
		GROUP BY lower_user_name, directory_id 
		HAVING (COUNT(lower_user_name) > 1);

Resolution 1 - MS SQL database

If any of your applications (Confluence/JIRA) are using MS SQL Server as its database, check that the transaction isolation level is set to "Read Committed" (check the guides below for further reference) before following the resolution steps. If the isolation level is not set properly you will need to shutdown the application, fix the isolation level as described in the guides below, and then proceed with the resolution steps.

• Database Setup for SQL Server - Confluence

Resolution 2 - Recreate the user directory

1. Go to Confluence Admin -> User Directories and manually synchronize the directory by hitting the synchronize button for the directory that the user belongs to
2. If that does not correct the issue, place the Confluence Internal Directory in the first position under User Directories
3. Login with an internal administrator of Confluence, navigate to Confluence Administration > User Directories, and disable the directory that the affected user belongs to
4. Create a new directory with the exact same configuration as the old directory
   

5. Synchronize the new directory, and test if the issue persists
   
   

   If the user directory in question is set up as "Read-only with Local Groups", the new directory you create will not retain the local group memberships, and so those will have to be recreated. If this is not feasible, proceed with Resolution 3 instead.

   If the User Directory in question is a DELEGATING directory, creating a new copy as per Step (4) below will actually clear out all users and show the users as Unknown User in Confluence. This is expected and will fix itself as each user logs in again. This is because DELEGATING directory does not create the user in Confluence until the user first logs in.

6. If the problem is resolved (i.e., the user can log in as expected), remove the old directory and keep the new one 

7. If the problem persists, try resolution 3

Resolution 3 - Deleting the user from the database

Step 1: Find the duplicated user IDs

Run the query below to get the users IDs of the duplicated user(s):

SELECT id, lower_user_name FROM cwd_user WHERE external_id = '<duplicated external_id from diagnostic query>' 

or

SELECT id, lower_user_name FROM cwd_user WHERE lower_user_name = '<duplicated username from diagnostic query>' 

The result should be as follow:

Note the IDs provided in the query above. Determine which entry to delete in the next step (Step 2).

Step 2: Determine which IDs to delete by checking if any of the duplicated users currently belong to any group

Run the following SQL query against the duplicated IDs result. The SQL query will yield the number of groups that each duplicated_user ID is tied to:

SELECT count(*), child_user_id FROM cwd_membership WHERE child_user_id in (<duplicated_id_1>,<duplicated_id_2>) GROUP BY child_user_id

Step 3.1: Should one of the duplicated ID does not return any group membership result

If the SQL query above return with something as follows,

Proceed with safely deleting the other duplicated user id (not the one shown from the SQL query):

DELETE FROM cwd_user_attribute WHERE user_id = <duplicated_id>; 
DELETE FROM cwd_user_credential_record WHERE user_id = <duplicated_id>;
DELETE FROM cwd_user WHERE id = <duplicated_id>;

Step 3.2: Should both of the duplicated ID does return a number of group membership 

If the SQL query in step 2 return with something as follows:

Run the following SQL query to obtain the duplicated users group membership:

SELECT
u.user_name,
g.id as GroupID,
g.group_name
FROM
cwd_user u
JOIN cwd_membership m
ON
u.id = m.child_user_id
JOIN cwd_group g
ON
g.id = parent_id
WHERE u.user_name = '<duplicated username>'
ORDER BY 2 DESC, 1 ASC;

Take note of the result of the above SQL query. Proceed with safely deleting one of the duplicated user ids. Run the below queries in order:

DELETE FROM cwd_membership WHERE child_user_id = <duplicated_id>;
DELETE FROM cwd_user_attribute WHERE user_id = <duplicated_id>; 
DELETE FROM cwd_user_credential_record WHERE user_id = <duplicated_id>;
DELETE FROM cwd_user WHERE id = <duplicated_id>;

After removing the duplicates, the constraints in the Workaround section of  CONFSERVER-58261 - Getting issue details... STATUS  will need to be applied to avoid future duplicates. If errors are returned applying any constraint, take a screenshot and open a ticket with Atlassian Support.

Go to the Confluence UI afterward and ensure that the user has correct memberships.