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.

Last modified on Mar 30, 2016

Was this helpful?

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