Error importing backup due to unique constraint or cannot INSERT INTO NULL 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

Summary

Problem

  • Importing a backup to a new instance with Oracle Database.

The following error occurred appears during import.

Error 1

1 Error importing data: org.xml.sax.SAXException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:NodeAssociation][sinkNodeId,15095][sourceNodeId,115572][sourceNodeEntity,Issue][associationType,IssueVersion][sinkNodeEntity,Version] (SQL Exception while executing the following:INSERT INTO nodeassociation (SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE) VALUES (?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JSP273447.PK_NODEASSOCIATION) violated )) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:NodeAssociation][sinkNodeId,15095][sourceNodeId,115572][sourceNodeEntity,Issue][associationType,IssueVersion][sinkNodeEntity,Version] (SQL Exception while executing the following:INSERT INTO nodeassociation (SOURCE_NODE_ID, SOURCE_NODE_ENTITY, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE) VALUES (?, ?, ?, ?, ?, ?) (ORA-00001: unique constraint (JSP273447.PK_NODEASSOCIATION) violated ))

Error 2

1 Error importing data: org.xml.sax.SAXException: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:UserAssociation][created,2015-11-09 19:39:40.0][associationType,WatchIssue][sourceName,test][sinkNodeEntity,Issue] (SQL Exception while executing the following:INSERT INTO userassociation (SOURCE_NAME, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE, CREATED) VALUES (?, ?, ?, ?, ?, ?) (ORA-01400: cannot insert NULL into ("JSP273447"."USERASSOCIATION"."SINK_NODE_ID") )) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:UserAssociation][created,2015-11-09 19:39:40.0][associationType,WatchIssue][sourceName,test][sinkNodeEntity,Issue] (SQL Exception while executing the following:INSERT INTO userassociation (SOURCE_NAME, SINK_NODE_ID, SINK_NODE_ENTITY, ASSOCIATION_TYPE, SEQUENCE, CREATED) VALUES (?, ?, ?, ?, ?, ?) (ORA-01400: cannot insert NULL into ("JSP273447"."USERASSOCIATION"."SINK_NODE_ID") ))

Diagnosis

Environment

  • New JIRA instance with Oracle database.

Diagnostic Steps

  • Import backup into new instance.

Cause

Cause 1: Error due to duplicate record triggers failure of import with Error 1.

Cause 2: Error due to NULL value triggers failure of import with Error 2.

Solution

Resolution

Cause 1 Resolution:

  • Unzip backup file and open entities.xml file.

  • Identify the format of GenericEntity which is NodeAssociation in this case.

    Format

    1 <NodeAssociation sourceNodeId="115572" sourceNodeEntity="Issue" sinkNodeId="15095" sinkNodeEntity="Version" associationType="IssueVersion"/>|

  • Insert the value of each statement above based on Error 1.

    Error 1

    1 while inserting: [GenericEntity:NodeAssociation][sinkNodeId,15095][sourceNodeId,115572][sourceNodeEntity,Issue][associationType,IssueVersion][sinkNodeEntity,Version]

  • At the command line, use the following command to identify the number of duplicate.

    Command

    1 grep -i "<UserAssociation sourceName=\"test\" sourceNodeEntity=\"Issue\" sinkNodeId=\"15095\" sinkNodeEntity=\"Version\" associationType=\"WatchIssue\"" entities.xml

  • Open entities.xml and remove the total number of duplicate.(Eg.. 10 record found, remove only 9)

  • Save the file and zip both the activeobjects.xml and entities.xml file.

  • Perform the import.

Cause 2 Resolution:

  • Unzip backup file and open entities.xml file.

  • Identify the format of GenericEntity which is UserAssociation in this case. (where sinkNodeId is a value based on the issue)

    Format

    1 <UserAssociation sourceName="test" sinkNodeId="*" sinkNodeEntity="Issue" associationType="WatchIssue" created="2015-11-09 19:39:40.0"/>

  • Locate the data line by finding created="2015-11-09 19:39:40.0".

  • Result of the finding show that the data line is as below. The sinkNodeID is missing in this case.

    Command

    1 <UserAssociation sourceName="test" sinkNodeEntity="Issue" associationType="WatchIssue" created="2015-11-09 19:39:40.0"/>

  • Remove the data line and save the file.

  • Zip both the activeobjects.xml and entities.xml file.

  • Perform the import.

Updated on April 2, 2025
Platform
Data Center only
Product
Jira Software Data Center
On this pageSummaryDiagnosisCauseSolution

Still need help?

The Atlassian Community is here for you.
Ask the Community