JIRA Upgrade Fails

[Rathna]

I am looking to upgrade an environment which is as follows:

OS : SLES

JIRA VERSION : 5.2.11

Want to upgrade to 6.4.12

What is the best solution to get there. I am not asking the easier way out but would like to know if there is instead of going through the pain taking route of upgrading to every version between 5.2.11 and 6.4.12

Errors:

JIRA Access Constraints

You cannot access JIRA at present. 

Description	Time	Level	Exception
An error occurred performing JIRA upgrade The data before the upgrade has been exported to /data/.../jira...104.zip	2016-01-04 11:31:04	error	Exception thrown during upgrade: Could not create index: SQL Exception while executing the following: CREATE UNIQUE INDEX issue_proj_num ON jiraissue (issuenum, PROJECT) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '10-10712' for key 'issue_proj_num' java.lang.RuntimeException: Could not create index: SQL Exception while executing the following: CREATE UNIQUE INDEX issue_proj_num ON jiraissue (issuenum, PROJECT) Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '10-10712' for key 'issue_proj_num' at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6132.createIndex(UpgradeTask_Build6132.java:60) at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build6132.doUpgrade(UpgradeTask_Build6132.java:50) at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeTaskSuccess(UpgradeManagerImpl.java:685) at com.atlassian.jira.upgrade.UpgradeManagerImpl.runUpgradeTasks(UpgradeManagerImpl.java:534) at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgrade(UpgradeManagerImpl.java:463) at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeIfNeeded(UpgradeManagerImpl.java:405) at com.atlassian.jira.upgrade.UpgradeManagerImpl.doUpgradeIfNeededAndAllowed(UpgradeManagerImpl.java:340) at com.atlassian.jira.upgrade.UpgradeLauncher.checkIfUpgradeNeeded(UpgradeLauncher.java:106) at com.atlassian.jira.upgrade.UpgradeLauncher.start(UpgradeLauncher.java:54) at com.atlassian.jira.startup.ActiveServicesLauncher.start(ActiveServicesLauncher.java:42) at com.atlassian.jira.startup.DefaultJiraLauncher$3.run(DefaultJiraLauncher.java:132) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:317) at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:211) at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:118) at com.atlassian.jira.startup.DefaultJiraLauncher.access$100(DefaultJiraLauncher.java:32) at com.atlassian.jira.startup.DefaultJiraLauncher$1.run(DefaultJiraLauncher.java:81) at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:34) at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:76) at com.atlassian.jira.startup.LauncherContextListener.contextInitialized(LauncherContextListener.java:54) at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4939) at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5434) at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1559) at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1549) at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303) at java.util.concurrent.FutureTask.run(FutureTask.java:138) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918) at java.lang.Thread.run(Thread.java:662)

I have gone thru this Link which does not work.

[Jeremy Gaudet · Accepted solution]

As I read it, the issue is that you have two issues with the same project/issuenum pair, "10-10712", in your mysql JIRA database.  Whether or not that means project 10, issue 10712, or project 10712, issue 10, is uncertain.  Either way, the only way to create that index is to change the issuenum(s) of the affected duplicates.

Step 1 would be to identify the rows.  Something like:

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

From there, get the IDs of all matching records.  Assuming there's just the one, and it's issuenum 10, project 10712, it would be:

select id, summary from jiraissue where issuenum=10 and project=10712;

There should be multiple rows here.   The summary may help identify which one, if any, is legitimate.  If one is obviously incorrect, you can just delete it by ID:

delete from jiraissue where id=X;

That would certainly be easiest; the alternative is to grab the next available value for this project:

select pcounter from project where id=10712;

Followed by:

update jiraissue set issuenum=<project counter + 1> where id=X;
update project set pcounter=pcounter+1 where id=10712;

The second update is likely necessary to tell JIRA that the ID you just assigned has been used, otherwise I'd expect the next "create issue" attempt to fail.  However, I've not tested that case, it's just highly reasonable.

[Jeremy Gaudet]

Actually, deleting the issue by ID would be incomplete, as you'd also have to delete all references. Adjusting the ID and deleting via the UI is the better approach, assuming the delete would even go through.

[Jeremy Gaudet]

Oh, also... forget using the max ID as the next value, as then you could theoretically be re-using a deleted ID (unlikely, but possible). It would be best to use the definitive value in project, "select pcounter from project where id=10712", and add one to that instead.

[Rathna]

Hi Jeremy,

Thanks for the quick and accurate response.