Violation of UNIQUE KEY Constraint when Upgrading JIRA
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
Summary
Symptoms
Upgrading JIRA fails with this error:
2015-04-03 19:14:17,007 localhost-startStop-1 ERROR [atlassian.jira.upgrade.UpgradeManagerImpl] Exception thrown during upgrade: Violation of UNIQUE KEY constraint 'pkey_unique'. Cannot insert duplicate key in object 'dbo.jiraissue'. The duplicate key value is (<NULL>).
java.sql.SQLException: Violation of UNIQUE KEY constraint 'pkey_unique'. Cannot insert duplicate key in object 'dbo.jiraissue'. The duplicate key value is (<NULL>).
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:671)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:572)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:727)
...Diagnosis
JIRA is being upgraded from a version older than 6.1 to one newer than (or equal to) 6.1, using the Installer.
According to the stacktrace, table jiraissue has a Unique Key constraint which prevents NULL values from being inserted into a column multiple times. This constraint can be viewed from the table structure:
CONSTRAINT [pkey_unique] UNIQUE NONCLUSTERED
(
[pkey] ASC
)Cause
In JIRA 6.0.x and below, table jiraissue contains pkey column which consists of Unique issue keys
In JIRA 6.1.x and above, this column is not used any more: project key is removed while issue number is extracted and stored into issuenum column, the pkey column still exists but holds a NULL value for every issue
Because of this, the error is thrown and the upgrade task fails. JIRA can't insert a duplicate NULL value into this column.
Solution
Resolution
Since this is an in-place upgrade using the Installer, data must have been backed up
Run this SQL query against the JIRA database to drop the constraint:
ℹ️ By default, this constraint doesn't exist (the constraint name can be found from the stacktrace above)
alter table jiraissue drop constraint pkey_unique;Run the Installer to upgrade Jira again
Was this helpful?