Violation of UNIQUE KEY Constraint when Upgrading JIRA
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) ...
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 )
- 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
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.
- 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