java.sql.SQLException: pk_AO_60DB71_SPRINTMARKER_ID is not a constraint when reindexing JIRA
You are attempting to upgrade JIRA and JIRA Agile with the following upgrade path:
- JIRA 5.2.11 to 6.0.x
- JIRA Agile 6.2.1 to 6.2.x
You upgrade JIRA first by installing the new version in a new file location, and pointing the new version to a copy of the old version's database. (Note: this is not an in-place database upgrade)
Next, you log in to the new JIRA and download the new JIRA Agile version from the UPM. The plugin installation will tell you that it is successful. However, running a reindex after that throws the following exceptions:
Caused by: com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library: Database: name:Microsoft SQL Server version:10.50.4000 minor version:50 major version:10 Driver: name:jTDS Type 4 JDBC Driver for MS SQL Server and Sybase version:1.2.4 java.sql.SQLException: 'pk_AO_60DB71_SPRINTMARKER_ID' is not a constraint. at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.migrate(EntityManagedActiveObjects.java:47) at com.atlassian.activeobjects.internal.AbstractActiveObjectsFactory.create(AbstractActiveObjectsFactory.java:52) at com.atlassian.activeobjects.internal.DelegatingActiveObjectsFactory.create(DelegatingActiveObjectsFactory.java:39) at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory.createActiveObjects(ActiveObjectsServiceFactory.java:136) at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory.access$000(ActiveObjectsServiceFactory.java:51) at com.atlassian.activeobjects.osgi.ActiveObjectsServiceFactory$1$1.get(ActiveObjectsServiceFactory.java:65) ... ... 3 more Caused by: java.sql.SQLException: 'pk_AO_60DB71_SPRINTMARKER_ID' is not a constraint.
Important Notes: The affected database seems to be Microsoft SQL Server 2008 R2 only. It is not entirely known that this problem also affects other versions of JIRA or JIRA Agile. However, please feel free to share it in this article if such situations should occur.
Since the upgrade procedure involves creating a copy of the production database, the first thing to check is the CREATE scripts for the AO_60DB71_SPRINTMARKER table. The CREATE script seems to be creating the constraint correctly.
CREATE TABLE [dbo].[AO_60DB71_SPRINTMARKER]( [ID] [bigint] NOT NULL, [MARKER_CONTEXT] [bigint] NOT NULL, [NAME] [varchar](255) NOT NULL, CONSTRAINT [pk_AO_60DB71_SPRINTMARKER_ID] PRIMARY KEY CLUSTERED
Reinstalling JIRA Agile does not help.
The exact cause is unknown.
Workaround: Upgrade JIRA Agile before upgrading JIRA
- Create a staging instance of your production JIRA 5.2.11. (Using a copy of the production database as you did when upgrading)
- Upgrade JIRA Agile on test instance.
- Reindex. Make sure JIRA Agile is working correctly after the re-index. Check the log files for errors relating to 'pk_AO_60DB71_SPRINTMARKER_ID' is not a constraint.
- Next, upgrade the staging JIRA instance using in-place database upgrade, here: documentation