User related issues in Confluence with the error message 'query did not return a unique result'
プラットフォームについて: Server と Data Center のみ - この記事は、サーバーおよびデータセンター プラットフォームのアトラシアン製品にのみ適用されます。
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
org.springframework.dao.IncorrectResultSizeDataAccessException: query did not return a unique result: 2
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-58261Getting 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)
- 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.
- If the second stack trace was identified, this means that there are duplicated records in the user_mapping table. Proceed to the following page:
- If the third stack trace was identified, this means that there are duplicated records in the cwd_group table. Proceed to the following page:
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
Make sure to perform a backup of the Confluence database and its application directories before attempting this procedure. In case anything goes wrong we can revert Confluence to a previous working state.
- Go to Confluence Admin -> User Directories and manually synchronize the directory by hitting the synchronize button for the directory that the user belongs to
- If that does not correct the issue, place the Confluence Internal Directory in the first position under User Directories
- Login with an internal administrator of Confluence, navigate to Confluence Administration > User Directories, and disable the directory that the affected user belongs to
- Create a new directory with the exact same configuration as the old directory
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.
If the problem is resolved (i.e., the user can log in as expected), remove the old directory and keep the new one
If the problem persists, try resolution 3
Resolution 3 - Deleting the user from the database
Always backup Confluence before removing or modifying any data on it
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>'
SELECT id, lower_user_name FROM cwd_user WHERE lower_user_name = '<duplicated username from diagnostic query>'
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-58261Getting 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.