Reindex fail due to duplicate issue keys in Jira
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
Full reindex fails due to the fact that there are two or more issues with the same key, which isn't expected by Jira.
Environment
All versions of Jira Core 7.x, 8.x and 9.x.
Diagnosis
Checking the logs, we can see the message that "Indexing Completed with 2 errors":
2023-11-01 02:06:43,721+0000 JiraTaskExecutionThread-1 ERROR jira_admin 1299x677x1 xxxx xxx.xxx.xxx.xxx /secure/admin/IndexReIndex!reindex.jspa [c.a.j.util.index.CompositeIndexLifecycleManager] Reindexing encountered an error for one of the indexers. Reindex all continues. Error for indexer: DefaultIndexManager: paths: [/var/atlassian/application-data/jira/caches/indexesV2/comments, /var/atlassian/application-data/jira/caches/indexesV2/issues, /var/atlassian/application-data/jira/caches/indexesV2/changes, /var/atlassian/application-data/jira/caches/indexesV2/worklogs, /var/atlassian/application-data/jira/caches/indexesV2/plugins/JEEntity, /var/atlassian/application-data/jira/caches/indexesV2/plugins/servicedeskcannedresponses]
com.atlassian.jira.index.IndexingFailureException: Indexing completed with 2 errors
at com.atlassian.jira.index.AccumulatingResultBuilder$CompositeResult.await(AccumulatingResultBuilder.java:151)
at com.atlassian.jira.issue.index.DefaultIssueIndexer.doIssuesOperationBatchMode(DefaultIssueIndexer.java:263)And going back in the logs, we can see the following error:
2023-10-31 21:55:39,467+0000 JiraTaskExecutionThread-1 WARN jira_admin 1299x677x1 xxxx xxx.xxx.xxx.xxx /secure/admin/IndexReIndex!reindex.jspa [c.a.jira.index.AccumulatingResultBuilder] java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Duplicate key EntityWithVersion{entity=TEST-123, version=15} (attempted merging values com.atlassian.jira.task.context.PercentageContext$1@6c1b8a4 and com.atlassian.jira.task.context.PercentageContext$1@6c1b8a4)
java.lang.RuntimeException: java.util.concurrent.ExecutionException: java.lang.IllegalStateException: Duplicate key EntityWithVersion{entity=TEST-123, version=15} (attempted merging values com.atlassian.jira.task.context.PercentageContext$1@6c1b8a4 and com.atlassian.jira.task.context.PercentageContext$1@6c1b8a4)
at com.atlassian.jira.index.FutureResult.await(FutureResult.java:29)
at com.atlassian.jira.index.AccumulatingResultBuilder.collectResult(AccumulatingResultBuilder.java:93)
at com.atlassian.jira.index.AccumulatingResultBuilder.addInternal(AccumulatingResultBuilder.java:58)
at com.atlassian.jira.index.AccumulatingResultBuilder.add(AccumulatingResultBuilder.java:35)
at com.atlassian.jira.issue.index.DefaultIssueIndexer.doIssuesOperationBatchMode(DefaultIssueIndexer.java:261)
at com.atlassian.jira.issue.index.DefaultIssueIndexer.indexIssuesBatchMode(DefaultIssueIndexer.java:241)Root cause 1 - issues with duplicate issue numbers
If the error is like the example above, "entity=TEST-123" refers to the problematic issue key, and when trying to access this particular issue through the Jira UI will throw a 500 internal server error.
Root cause 2 - issues with broken project references
If the error has the issue summary instead of the key (for example "entity=My issue summary") then the issue has a broken project reference.
Solution
Please make sure to back up your database before performing any changes.
Solution for root cause 1
The query below should list all duplicate issues and their respective internal IDs:
select p.id as "Project ID", p.pkey as "Project Key", a.issuenum as "Issue Num", a.id as "Issue A ID", b.id as "Issue B ID"
from jiraissue a
join jiraissue b on a.issuenum = b.issuenum and a.project = b.project and a.id < b.id
join project p on a.project = p.id
where a.id is not null;Stop Jira (if in a multinode environment, stop all nodes) and perform the following update for each of the entries returned by the query above, replacing <Project ID> and <Issue B ID> accordingly:
update jiraissue set issuenum = (select (max(issuenum) + 1) from jiraissue where project = <Project ID>) where id = <Issue B ID>;Start Jira and perform a Full Reindex.
Solution for root cause 2
- The query below should get the issues that reference projects that don't exist:
SELECT DISTINCT i.id, i.project, i.summary, i.issuenum, p.id, p.pkey FROM jiraissue i LEFT JOIN project p on i.project=p.id WHERE p.id IS NULL
- Create a new project;
- Make sure there are no issues in this new project;
- Navigate into the project settings, then into the Details page;
- Take note of the ID in the end of the URL. For example, /secure/project/EditProject!default.jspa?pid=10123 means this project ID is 10123
- Run this query to add each issue to the project:
UPDATE jiraissue SET project = <Project_ID> WHERE id = <Issue_ID>;
Make sure that the issuenum column is not a duplicate with other issues added, or we'll get to the root cause 1 situation. - Restart Jira and perform a full reindex, or a project reindex in the new project.
- Optionally, delete this new project with the issues added.
Alternative workaround for either root cause
A workaround to get the index working is to set the archived column to Y so Jira doesn't load those issues and doesn't get into the duplicate errors.
This is a temporary workaround, and should be used only when we need a quick solution. After this is done, either of the actual solutions above must be performed.
This workaround doesn't require Jira to be down, but we strongly recommend a restart and reindex after thequery is run.
This is the workaround for root cause 1:
UPDATE jiraissue
SET archived='Y'
WHERE id in (select b.id
from jiraissue a
join jiraissue b on a.issuenum = b.issuenum and a.project = b.project and a.id < b.id
join project p on a.project = p.id
where a.id is not null;)This is the workaround for root cause 2:
UPDATE jiraissue
SET archived='Y'
WHERE id in (SELECT DISTINCT i.id
FROM jiraissue i
LEFT JOIN project p on i.project=p.id
WHERE p.id IS NULL)