Upgrading To JIRA 6.1 or later Fails Due To Duplicate Issue Keys
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
Symptoms
Upgrade fails because JIRA can not create unique index.
The following appears in the atlassian-jira.log:
1
2
3
ERROR [atlassian.jira.upgrade.UpgradeManagerImpl] Exception thrown during upgrade: Could not create index: SQL Exception while executing the following:
CREATE UNIQUE INDEX issue_proj_num ON PUBLIC.jiraissue (issuenum, PROJECT)
Error was: java.sql.SQLException: Violation of unique index in statement [CREATE UNIQUE INDEX issue_proj_num ON PUBLIC.jiraissue (issuenum, PROJECT)]Cause
The pair of columns PROJECT and ISSUENUM of table JIRAISSUE does not satisfy the uniqueness constraint. This is because the contents of PKEY column from before the upgrade was inconsistent. There could have been two different types of inconsistency:
Column PKEY contained duplicate issue keys.
Column PKEY contained issue keys inconsistent with PROJECT column. That is PROJECT column contained identifier of project that in PROJECT table had PKEY of e.g 'ABC' while column PKEY of JIRAISSUE table had different project prefix e.g. 'TST-1' (while it should be ABC-1 or other with ABC prefix).
Resolution
Restore JIRA instance from backup
Find offending issues with one of the following sql queries:
Look for duplicate issue keys:
1select issuenum, project from jiraissue group by issuenum, project having count(*) > 1;
Look for issue keys with incorrect project prefix.
1select ji.id, ji.pkey, ji.project, p.id, p.pkey from jiraissue ji join project p on ji.project = p.id where substring(ji.pkey from 1 for position('-' in ji.pkey)-1) != p.pkey;
Resolve duplicates and inconsistencies either by removing incorrect issues or by changing pkey to unique one
ℹ️ Use the pcounter column from a PROJECT table to determine next available issue number, by incrementing the value by 1.
Was this helpful?