[BSERV-4413] Unique key constraints not working for Embedded Crowd table cwd_membership

Type: Bug
Resolution: Fixed
Priority: High
Fix Version/s: 3.7.0
Affects Version/s: 2.9.4, 2.10.1
Component/s: Administration - Users and Groups
Labels:

Bug Fix Policy:
View Atlassian Server bug fix policy

Crowd can generate duplicate rows Crowd for memberships. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in cwd_membership.

org.hibernate.NonUniqueResultException: query did not return a unique result: 2
	at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
	at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
	at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
	at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
	...

Workaround

Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

Additional note

The fix for this issue inserted the uk_mem_dir_parent_child into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:

2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement

If you're hitting that, please proceed with the following steps:

Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in Administration >> User Directories
Delete the old one
The issue won't return as the constraint will never allow Crowd's bug manifest again.

is related to

BSERV-5244 Unique key constraints not working for Embedded Crowd table cwd_user

Closed

relates to

CONFSERVER-35047 Confluence user synchronisation create duplicated user account after user rename in the remote repository

Closed

is caused by: EMBCWD-994 Failed to load

mentioned in: Unable to sync crowd user directory - query did not return a unique result; Page No Confluence page found with the given URL.; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...; Page Loading...

(8 mentioned in)

Form Name

Owen made changes - 10/Apr/2019 6:46 AM

Workflow

Original: Stash Workflow - Restricted [ 1445249 ]

New: JAC Bug Workflow v3 [ 3135569 ]

Idan Bidani added a comment - 17/Sep/2018 7:43 PM

Thanks Bryan, issue opened

Idan Bidani added a comment - 17/Sep/2018 7:43 PM Thanks Bryan, issue opened

Bryan Turner (Inactive) added a comment - 17/Sep/2018 7:15 PM

idan.bidani,

That error means the unique constaints are working. Put differently, it shows that this issue is fixed. Previously, the duplicate insert would have just worked, and corrupted the data.

That error should be benign (it shouldn't happen at all, of course, but when it does it should be benign). Are you seeing it often? When does it happen? You might want to open a ticket at support.atlassian.com, where you can share details and logs with us privately, rather than trying to diagnose the problem here.

Best regards,
Bryan Turner
Atlassian Bitbucket

Bryan Turner (Inactive) added a comment - 17/Sep/2018 7:15 PM idan.bidani , That error means the unique constaints are working. Put differently, it shows that this issue is fixed. Previously, the duplicate insert would have just worked, and corrupted the data. That error should be benign (it shouldn't happen at all, of course, but when it does it should be benign). Are you seeing it often? When does it happen? You might want to open a ticket at support.atlassian.com, where you can share details and logs with us privately, rather than trying to diagnose the problem here. Best regards, Bryan Turner Atlassian Bitbucket

Idan Bidani added a comment - 17/Sep/2018 7:09 PM

I'm seeing this error on Bitbucket 5.6.2 with Postgres

Any idea what could cause it to happen if this was fixed?

Thanks

< 2018-09-17 10:18:28.039 EDT > ERROR:  duplicate key value violates unique constraint "uk_mem_dir_parent_child"
< 2018-09-17 10:18:28.039 EDT > DETAIL:  Key (directory_id, lower_child_name, lower_parent_name, membership_type)=(4325377, <<REAL_USERID>>, <<REAL_DL_NAME>>, GROUP_USER) already exists.
< 2018-09-17 10:18:28.039 EDT > STATEMENT:  insert into cwd_membership (parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, created_date, directory_id, id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

Idan Bidani added a comment - 17/Sep/2018 7:09 PM I'm seeing this error on Bitbucket 5.6.2 with Postgres Any idea what could cause it to happen if this was fixed? Thanks < 2018-09-17 10:18:28.039 EDT > ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child" < 2018-09-17 10:18:28.039 EDT > DETAIL: Key (directory_id, lower_child_name, lower_parent_name, membership_type)=(4325377, <<REAL_USERID>>, <<REAL_DL_NAME>>, GROUP_USER) already exists. < 2018-09-17 10:18:28.039 EDT > STATEMENT: insert into cwd_membership (parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, created_date, directory_id, id) values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)

vkharisma made changes - 01/Apr/2017 2:13 PM

Link

New: This issue relates to CONFCLOUD-35047 [ CONFCLOUD-35047 ]

ThiagoBomfim (Inactive) made changes - 26/Oct/2016 5:39 AM

Description

Original: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

{panel:title=Additional note|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}

The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.
{panel}

New: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.
{code:java}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...

{code}
h4. Workaround

Remove and reconnect the affected Crowd directory. More details:
[https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result]
{panel:title=Additional note}
The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:
{code:java}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement

{code}
If you're hitting that, please proceed with the following steps:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.{panel}

ThiagoBomfim (Inactive) made changes - 26/Oct/2016 5:34 AM

Description

Original: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

{panel:title=Issue Analysis by Thiago Bomfim (Restricted to Atlassian)|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}
h4. Additional note
The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.
{panel}

New: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

{panel:title=Additional note|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}

The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.
{panel}

ThiagoBomfim (Inactive) made changes - 26/Oct/2016 5:34 AM

Description

Original: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

h4. Additional note
The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task, you could start having your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.

New: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

{panel:title=Issue Analysis by Thiago Bomfim (Restricted to Atlassian)|borderStyle=dashed|borderColor=#ccc|titleBGColor=#F7D6C1|bgColor=#FFFFCE}
h4. Additional note
The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task. This will get your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.
{panel}

ThiagoBomfim (Inactive) made changes - 24/Oct/2016 6:13 AM

Description

New: Crowd can generate duplicate rows Crowd *for memberships*. This causes exceptions when Stash tries to sync with crowd.

Stash has constraints in {{cwd_membership}}.

{code}
org.hibernate.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.AbstractQueryImpl.uniqueElement(AbstractQueryImpl.java:914) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at org.hibernate.internal.CriteriaImpl.uniqueResult(CriteriaImpl.java:396) ~[hibernate-core-4.2.5.Final.jar:4.2.5.Final]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.findInternalMembership(MembershipDAOHibernate.java:356) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
at com.atlassian.crowd.dao.membership.MembershipDAOHibernate.isUserDirectMember(MembershipDAOHibernate.java:211) ~[crowd-persistence-hibernate4-2.7.1-rc.jar:na]
...
{code}

h4. Workaround
Remove and reconnect the affected Crowd directory. More details:
https://confluence.atlassian.com/display/STASHKB/Unable+to+sync+crowd+user+directory+-+query+did+not+return+a+unique+result

h4. Additional note
The fix for this issue inserted the {{uk_mem_dir_parent_child}} into a table on our database. After upgrading to versions above that fix, you should get the duplicate records removed from the database and all fixed.

If your database was in bad shape you could still have a few duplicate records that hadn't been deleted during the upgrade task, you could start having your synchronisation failing with the following error:
{code}
2016-08-25 18:07:57,939 ERROR [Caesium-1-4] o.h.e.jdbc.spi.SqlExceptionHelper ERROR: duplicate key value violates unique constraint "uk_mem_dir_parent_child"
2016-08-25 18:07:57,963 ERROR [Caesium-1-4] c.a.c.d.DbCachingDirectoryPoller Error occurred while refreshing the cache for directory [ 2457601 ].
org.hibernate.exception.ConstraintViolationException: could not execute statement
{code}

If you're hitting that, please proceed with the following step:
# Create a copy of your External Directory connection (i.e. LDAP, JIRA, etc) in {{Administration >> User Directories}}
# Delete the old one
# The issue won't return as the constraint will never allow Crowd's bug manifest again.

Owen made changes - 07/Jul/2016 4:03 AM

Workflow

Original: Stash Workflow [ 625844 ]

New: Stash Workflow - Restricted [ 1445249 ]

Assignee:: Justin Thomas

Reporter:: James Gorman

Affected customers:: 7 This affects my team

Watchers:: 15 Start watching this issue

Created:: 25/Feb/2014 5:00 AM

Updated:: 17/Sep/2018 7:43 PM

Resolved:: 28/Jan/2015 4:57 AM

Details

Description

Workaround

Attachments

Issue Links

Forms

Activity

Collapse comment: Idan Bidani added a comment - 17/Sep/2018 7:43 PM

Expand comment: Idan Bidani added a comment - 17/Sep/2018 7:43 PM

Collapse comment: Bryan Turner (Inactive) added a comment - 17/Sep/2018 7:15 PM

Expand comment: Bryan Turner (Inactive) added a comment - 17/Sep/2018 7:15 PM

Collapse comment: Idan Bidani added a comment - 17/Sep/2018 7:09 PM

Expand comment: Idan Bidani added a comment - 17/Sep/2018 7:09 PM

People

Dates

Backbone Issue Sync