症状

Upgrade fails because JIRA can not create unique index.

atlassian-jira.log に次のメッセージが表示される。

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

原因

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:

1. Column PKEY contained duplicate issue keys.
2. 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).

ソリューション

• Restore JIRA instance from backup
• Find offending issues with one of the following sql queries:

  • Look for duplicate issue keys:

    select issuenum, project from jiraissue group by issuenum, project having count(*) > 1;

• • Look for issue keys with incorrect project prefix.

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