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

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

The following error occurred appears during import.

Error 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
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.

Resolution

Cause 1 Resolution:

  • Unzip backup file and open entities.xml file. 
  • Identify the format of GenericEntity which is NodeAssociation in this case.

    Format
    <NodeAssociation sourceNodeId="115572" sourceNodeEntity="Issue" sinkNodeId="15095" sinkNodeEntity="Version" associationType="IssueVersion"/>| 
  • Insert the value of each statement above based on Error 1.

    Error 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
    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
    <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
    <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.

 

Last modified on Sep 2, 2016

Was this helpful?

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