Directory synchronization fails with Cannot insert duplicate key row in object 'dbo.cwd_membership' with unique index 'uk_mem_parent_child_type' error

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

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

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 more


Cause

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


Description
Product
Last modified on Sep 20, 2021

Was this helpful?

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