Encountered an error while restoring the XML backup during JIRA migration to another server

Question

Hi Everyone,

We are in the process of migrating from one JIRA server to another. The new JIRA installation is completed on a Linux machine, with JIRA version 9.4.1, and the database is PostgreSQL.

However, we are facing an issue while restoring the XML backup (in ZIP format). The following error occurs:

Error importing data: org.xml.sax.SAXException: java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:IssueVersion][issueId,241870][deleted,N][updateTime,2024-08-14 16:46:33.418968][indexVersion,15] (SQL Exception while executing the following:INSERT INTO public.issue_version (ISSUE_ID, PARENT_ISSUE_ID, UPDATE_TIME, INDEX_VERSION, DELETED) VALUES (?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_issue_version" Detail: Key (issue_id)=(241870) already exists.)) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:IssueVersion][issueId,241870][deleted,N][updateTime,2024-08-14 16:46:33.418968][indexVersion,15] (SQL Exception while executing the following:INSERT INTO public.issue_version (ISSUE_ID, PARENT_ISSUE_ID, UPDATE_TIME, INDEX_VERSION, DELETED) VALUES (?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "pk_issue_version" Detail: Key (issue_id)=(241870) already exists.))

Upon checking the database, I found that it already has a record for the issue with ID 241870:

select * FROM public.issue_version WHERE issue_id = 241870;

issue_id: 241870 update_time: 2024-08-14 13:16:33.418968 index_version: 15 deleted: N

However, the XML backup contains two records for the same issue ID:

IssueVersion issueId="241870" updateTime="2024-08-14 11:02:19.368702" indexVersion="14" deleted="N"

IssueVersion issueId="241870" updateTime="2024-08-14 16:46:33.418968" indexVersion="15" deleted="N"

Can anyone provide suggestions on how to resolve this duplicate key constraint issue during the restore process? Any help would be greatly appreciated!

Amir Hosseini_zwwwp7 · Answer

I removed the duplicate issue version tags in the XML backup. However, when I tried restoring the XML backup again, I encountered this error:

Error importing data: org.xml.sax.SAXException: java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:UserHistoryItem][lastViewed,1723641605768][entityId,241419][id,2458373][type,Issue][username,JIRAUSER19126] (SQL Exception while executing the following:INSERT INTO public.userhistoryitem (ID, entitytype, entityid, USERNAME, lastviewed, data) VALUES (?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "uh_type_user_entity" Detail: Key (entitytype, username, entityid)=(Issue, JIRAUSER19126, 241419) already exists.)) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:UserHistoryItem][lastViewed,1723641605768][entityId,241419][id,2458373][type,Issue][username,JIRAUSER19126] (SQL Exception while executing the following:INSERT INTO public.userhistoryitem (ID, entitytype, entityid, USERNAME, lastviewed, data) VALUES (?, ?, ?, ?, ?, ?) (ERROR: duplicate key value violates unique constraint "uh_type_user_entity" Detail: Key (entitytype, username, entityid)=(Issue, JIRAUSER19126, 241419) already exists.))

Amir Hosseini_zwwwp7 · Answer

I have an SQL backup that I do not want to use for many reasons. This error actually surprised me. Why did it happen?
Why should I delete a record in the XML backup? If there is another one how can I check it beforehand?
p.s:
In database there is a record for that.

Gonchik Tsymzhitov · Answer

Do you have sql backup in RDBMS or only xml backup ?

I am asking one way you can remove from xml backup or another run sql script to remove duplicates