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

[Amir Hosseini]

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]

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

[Gonchik Tsymzhitov]

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

[Amir Hosseini]

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.