Unique key constraints not working for Embedded Crowd table cwd_user

Crowd is querying cwd_user by external_id and is expecting unique results for users. There is no unique constraint on this column and in some conditions a crowd sync can cause duplicate external_id to be entered into the table.

Stack trace:

2015-05-13 02:37:56,086 ERROR [clusterScheduler_Worker-9]  c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 32770 ].
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
        at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:975) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
        at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:402) ~[hibernate-core-4.3.8.Final.jar:4.3.8.Final]
        at com.atlassian.crowd.dao.user.UserDAOHibernate.findByExternalId(UserDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.8.2-m5.jar:na]
        at com.atlassian.crowd.dao.user.UserDAOHibernate.findByExternalId(UserDAOHibernate.java:55) ~[crowd-persistence-hibernate4-2.8.2-m5.jar:na]
        ...

A fix should first check and remove duplicates that are orphaned from cwd_membership, then any remaining duplicate users, before creating the unique constraint.

Workaround

This can be manually fixed by deleting duplicate entries. It appears that the bad entries are missing a corresponding entry in cwd_membership, but this may not be true for all cases.
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

Fix

Stash 3.10 applies a new unique constraint to the relevant columns in the cwd_user table to ensure data consistency. If there are duplicate (directory_id, external_id) pairs when the system is upgraded to 3.10, they will be automatically repaired as part of upgrading.
This fix cannot be applied on SQL Server 2005. Due to limitations in SQL Server 2005, it is not possible to create a unique constraint or unique index on (directory_id, external_id) because external_id can be NULL. As a result, support for SQL Server 2005 is now deprecated. Stash will continue to run on SQL Server 2005, but this constraint will not be applied. As a result, it's possible instances running against SQL Server 2005 may continue to fail. Support for SQL Server 2005 will be fully removed in Stash 4.0. Administrators with Stash instances using SQL Server 2005 will be required to either update their SQL Server to 2008 or newer, or migrate to another supported database. Any instance still running on SQL Server 2005 will be unable to start after upgrading to 4.0.
After upgrading to 3.10, the exact failure described here is no longer possible. However, it may be possible for LDAP synchronization to fail if the new constraint is violated. Anyone whose instance encounters such a failure is encouraged to open a support request and provide details on the error. (This failure is very unlikely, but I want to note it here in case it does happen.)