Unable to sync crowd user directory - query did not return a unique result

Symptoms

The following appears in the 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
...

Diagnosis

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 duplicated external_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

Cause

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  

Resolution

Final Solution

For both scenarios, the final resolution is to upgrade your instance to the version that contains the appropriate fix.

Workaround

Removing and reconnecting the affected Crowd directory will fix this.

If that doesn't help you, please contact https://support.atlassian.com and provide the results for the queries above.