Directory synchronization fails with Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type' error
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
Directory synchronization fails with Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type' error. The following appears in the atlassian-jira.log
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Caused by: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Membership][lowerChildName,xxxxxx][membershipType,GROUP_USER][parentName,vcc-base-users][childName,XXXXXX][directoryId,10401][id,642169][childId,657301][lowerParentName,vcc-base-users][parentId,38313] (SQL Exception while executing the following:INSERT INTO dbo.cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type'. The duplicate key value is (38313, 657301, GROUP_USER).))
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:210)
at org.ofbiz.core.entity.GenericDAO.insert(GenericDAO.java:175)
at org.ofbiz.core.entity.GenericHelperDAO.create(GenericHelperDAO.java:89)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:566)
at org.ofbiz.core.entity.GenericDelegator.create(GenericDelegator.java:552)
at com.atlassian.jira.ofbiz.DefaultOfBizDelegator.createValue(DefaultOfBizDelegator.java:296)
... 14 more
Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:INSERT INTO dbo.cwd_membership (ID, parent_id, child_id, membership_type, group_type, parent_name, lower_parent_name, child_name, lower_child_name, directory_id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type'. The duplicate key value is (38313, 657301, GROUP_USER).)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:570)
at org.ofbiz.core.entity.GenericDAO.singleInsert(GenericDAO.java:202)
... 20 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type'. The duplicate key value is (38313, 657301, GROUP_USER).
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:256)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:592)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:522)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2930)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:471)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
at com.atlassian.jira.ofbiz.sql.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:47)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.lambda$executeUpdate$7(DiagnosticPreparedStatement.java:69)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement$$Lambda$1559/0x00000000d8027990.execute(Unknown Source)
at com.atlassian.diagnostics.internal.platform.monitor.db.DefaultDatabaseDiagnosticsCollector.recordExecutionTime(DefaultDatabaseDiagnosticsCollector.java:69)
at com.atlassian.jira.diagnostic.connection.DatabaseDiagnosticsCollectorDelegate.recordExecutionTime(DatabaseDiagnosticsCollectorDelegate.java:55)
at com.atlassian.jira.diagnostic.connection.DiagnosticPreparedStatement.executeUpdate(DiagnosticPreparedStatement.java:69)
at org.ofbiz.core.entity.jdbc.SQLProcessor.executeUpdate(SQLProcessor.java:562)
... 21 moreCause
It's still not clear yet why this issue happens and how the records get corrupted.
Resolution
Create a new Crowd Directory connection on Jira, with the same configuration from the existing one,
Move it above the current Crowd Directory in the directory order to test if the groups show up correctly in the new one, without the need to delete the old directory.
Keep the old directory disabled for a few days, just in case anyone notices any problem
Was this helpful?