Unable to sync crowd user directory - query did not return a unique result
症状
atlassian-stash.log
に次のメッセージが表示される。
2014-09-12 11:48:17,828 ERROR [scheduler_Worker-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ <directory ID> ].
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
...
診断
This is caused because Crowd can misbehave and generate duplicate entries in two different tables.
This might occur in 2 different scenarios. Please read on for more details.
Scenario 1: duplicate row in cwd_membership
:
In this case, you will see the following in the stack trace:
com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership
2014-09-12 11:48:17,828 ERROR [scheduler_Worker-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ <directory ID> ]. org.hibernate.NonUniqueResultException: query did not return a unique result: 2 at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.4.Final.jar:4.2.4.Final] at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.4.Final.jar:4.2.4.Final] at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:270) ~[crowd-persistence-hibernate4-2.6.6.jar:na] at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:146) ~[crowd-persistence-hibernate4-2.6.6.jar:na] at sun.reflect.GeneratedMethodAccessor1041.invoke(Unknown Source) ~[na:na] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_40] at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_40] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317) ~[spring-aop-3.2.4.RELEASE.jar:3.2.4.RELEASE] ...
Diagnosis: in order to diagnose duplicated rows in cwd_membership table you can use the following SQL query:
SELECT * FROM cwd_membership WHERE directory_id in (SELECT directory_id FROM cwd_membership GROUP BY directory_id, lower_parent_name, lower_child_name, membership_type HAVING COUNT(*) > 1) AND lower_parent_name in (SELECT lower_parent_name FROM cwd_membership GROUP BY directory_id, lower_parent_name, lower_child_name, membership_type HAVING COUNT(*) > 1) AND lower_child_name in (SELECT lower_child_name FROM cwd_membership GROUP BY directory_id, lower_parent_name, lower_child_name, membership_type HAVING COUNT(*) > 1) AND membership_type in (SELECT membership_type FROM cwd_membership GROUP BY directory_id, lower_parent_name, lower_child_name, membership_type HAVING COUNT(*) > 1);
Scenario 2: duplicate row in cwd_user
In this case, you will see the stack trace up to the following on the stack trace:
com.atlassian.crowd.dao.user.UserDAOHibernate.findByExternalId
2014-09-09 13:24:44,234 ERROR [clusterScheduler_Worker-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ <directory ID> ]. org.hibernate.NonUniqueResultException: query did not return a unique result: 2 at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:975) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:402) ~[hibernate-core-4.3.5.Final.jar:4.3.5.Final] at com.atlassian.crowd.dao.user.UserDAOHibernate.findByExternalId(UserDAOHibernate.java:204) ~[crowd-persistence-hibernate4-2.7.2.jar:na] at com.atlassian.crowd.dao.user.UserDAOHibernate.findByExternalId(UserDAOHibernate.java:50) ~[crowd-persistence-hibernate4-2.7.2.jar:na] ...
Diagnosis:
Query 1:
In order to diagnose duplicate users on
cwd_user
with duplicatedexternal_id
in Stash database, you can use the following SQL query:SELECT * FROM cwd_user WHERE external_id in (SELECT external_id FROM cwd_user GROUP BY external_id HAVING COUNT(*) > 1);
Query 2:
The query below should help us identify which row to remove in case a database intervention is needed. The values between parenthesis below should be the values of all the ID's obtained from the query immediately above this comment. The values are listed below just as an example of how the query should be structured.
select child_id, lower_child_name, lower_parent_name from cwd_membership where membership_type = 'GROUP_USER' and child_id in (131089, 133241, 133302, 327752, 327759, 327748, 327862, 924631, 924488, 924501, 924385, 924464) order by child_id
原因
Scenario 1: duplicated row in cwd_membership
:
- This issue is being tracked here: STASH-4413 - Getting issue details... STATUS
Scenario 2: duplicated row in cwd_user
- This issue is being tracked here: STASH-5244 - Getting issue details... STATUS
ソリューション
Final Solution
For both scenarios, the final resolution is to upgrade your instance to the version that contains the appropriate fix.
回避策
Removing and reconnecting the affected Crowd directory will fix this.
If that doesn't help you, please contact https://support.atlassian.com/ja and provide the results for the queries above.