Problem

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

The following error occurred appears during import.

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