Upgrade task failed for upgrading jira software serve from 7.6 to 8

[Mojtaba]

Dears,

We have faced below error for upgrading jira server from 7.6.2 to any version of jira 8.0.1, 8.0.2 with below error:

[c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=73011] failed

caused by:

ORA-00904: "HS"."ENTRY_ID": invalid identifier


the oracle database details:

Database version	Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
Database driver	Oracle JDBC driver 12.1.0.1.0

I have read below topic and xml restore solution is not possible because there is risk of data loss. also I have tried on another instance with empty database with the same db configuration and version and upgrade was fine from jira software 7.6.2 to 8 without issues. I guess it's related to the data which is available on the database, would you please advice what should we do? the data which is available on database is very important and we should fix this issue to do the upgrade to v8 family. 


https://confluence.atlassian.com/jirakb/upgrade-task-buildnumber-73011-failed-com-querydsl-core-queryexception-caught-sqlsyntaxerrorexception-938640144.html?_ga=2.165374463.1989865306.1552561503-751024907.1540909018




details are as below:

2019-03-14 18:25:12,840 JIRA-Bootstrap ERROR      [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=73011] failed

com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for insert into OS_CURRENTSTEP (ID, ENTRY_ID, STEP_ID, ACTION_ID, OWNER, START_DATE, DUE_DATE, FINISH_DATE, STATUS, CALLER)

select hs.ID, hs.ENTRY_ID, hs.STEP_ID, hs.ACTION_ID, hs.OWNER, hs.START_DATE, hs.DUE_DATE, null, hs.STATUS, hs.CALLER

from OS_HISTORYSTEP hs

where hs.ENTRY_ID in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) and exists (select ISSUE.id

from jiraissue ISSUE

where ISSUE.workflow_id = hs.ENTRY_ID and not exists (select OS_CURRENTSTEP.ID

from OS_CURRENTSTEP OS_CURRENTSTEP

where OS_CURRENTSTEP.ENTRY_ID = ISSUE.workflow_id)) and hs.ID = (select * from (

  select hs2.ID

from OS_HISTORYSTEP hs2

where hs.ENTRY_ID = hs2.ENTRY_ID and hs2.FINISH_DATE is not null and not exists (select prevStep.ID

from OS_HISTORYSTEP_PREV prevStep

where prevStep.PREVIOUS_ID = hs2.ID)

order by hs2.FINISH_DATE desc

) where rownum <= ?) and (select * from (

  select count(hs2.FINISH_DATE)

from OS_HISTORYSTEP hs2

where hs.ENTRY_ID = hs2.ENTRY_ID and hs2.FINISH_DATE is not null and not exists (select prevStep.ID

from OS_HISTORYSTEP_PREV prevStep

where prevStep.PREVIOUS_ID = hs2.ID)

group by hs2.FINISH_DATE

order by hs2.FINISH_DATE desc

) where rownum <= ?) = ?

at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)

at com.querydsl.sql.Configuration.translate(Configuration.java:459)

at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:445)

at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build73011.lambda$restoreEntriesFromHistory$3(UpgradeTask_Build73011.java:231)

at com.atlassian.jira.database.DefaultQueryDslAccessor.lambda$executeQuery$0(DefaultQueryDslAccessor.java:68)

at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:105)

at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:74)

at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:100)

at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:67)

at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build73011.restoreEntriesFromHistory(UpgradeTask_Build73011.java:197)

at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build73011.runUpgrade(UpgradeTask_Build73011.java:98)

at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.runOneUpgradeTask(DefaultUpgradeTaskFactoryProcessor.java:109)

at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.lambda$performUpgradesUnsafe$13(DefaultUpgradeTaskFactoryProcessor.java:80)

at java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:174)

at java.util.stream.SortedOps$RefSortingSink.end(SortedOps.java:395)

at java.util.stream.Sink$ChainedReference.end(Sink.java:258)

at java.util.stream.AbstractPipeline.copyIntoWithCancel(AbstractPipeline.java:499)

at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:485)

at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:471)

at java.util.stream.FindOps$FindOp.evaluateSequential(FindOps.java:152)

at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)

at java.util.stream.ReferencePipeline.findFirst(ReferencePipeline.java:464)

at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.performUpgradesUnsafe(DefaultUpgradeTaskFactoryProcessor.java:81)

at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.performUpgrades(DefaultUpgradeTaskFactoryProcessor.java:46)

at com.atlassian.upgrade.core.DefaultUpgradeTaskManager.upgradeHostApp(DefaultUpgradeTaskManager.java:41)

at com.atlassian.jira.upgrade.LicenseCheckingUpgradeService.executeUpgrades(LicenseCheckingUpgradeService.java:134)

at com.atlassian.jira.upgrade.LicenseCheckingUpgradeService.runUpgrades(LicenseCheckingUpgradeService.java:97)

at com.atlassian.jira.upgrade.ClusterLockingUpgradeService.runUpgrades(ClusterLockingUpgradeService.java:35)

at com.atlassian.jira.upgrade.LoggingUpgradeService.lambda$runUpgradesWithLogging$0(LoggingUpgradeService.java:28)

at com.atlassian.jira.upgrade.LoggingUpgradeService.runWithTaskLogging(LoggingUpgradeService.java:43)

at com.atlassian.jira.upgrade.LoggingUpgradeService.runUpgradesWithLogging(LoggingUpgradeService.java:28)

at com.atlassian.jira.upgrade.IndexingUpgradeService.runUpgrades(IndexingUpgradeService.java:19)

at com.atlassian.jira.upgrade.DefaultUpgradeService.runUpgrades(DefaultUpgradeService.java:56)

at com.atlassian.jira.upgrade.UpgradeLauncher.upgradeIfNeeded(UpgradeLauncher.java:109)

at com.atlassian.jira.upgrade.UpgradeLauncher.start(UpgradeLauncher.java:86)

at com.atlassian.jira.startup.ActiveServicesLauncher.start(ActiveServicesLauncher.java:50)

at com.atlassian.jira.startup.DefaultJiraLauncher.postDBActivated(DefaultJiraLauncher.java:172)

at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:146)

at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:301)

at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:196)

at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:137)

at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:104)

at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)

at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:102)

at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)

at java.lang.Thread.run(Thread.java:748)

Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "HS"."ENTRY_ID": invalid identifier

[Daniel Eads]

In addition to getting on Oracle 12c, I would strongly recommend using the release 2 driver for JIRA and Confluence (12.2.0.1) which you can obtain here from Oracle. The ojdbc8.jar file needs to go into the lib folder in your application install directory, and any older ojdbc8.jar/ojdbc7.jar files should be removed from that directory or renamed with a non-jar extension so that Jira doesn't try to load them on startup. Many users have experienced issues with the release 1 Oracle driver that it looks like you are currently running. This may not affect the situation you're in at this moment but from personal experience, using the release 2 driver can avoid some sticky situations where Oracle isn't behaving as expected.

[Mojtaba]

Thanks Dan,

Yeah, we'll use the latest driver with Oracle 12, also as you have suggested we already used the latest driver but it didn't solve the issue.

[Soley Gudmundsdottir]

Hi Mojtaba.

Did you get this resolved?

[Andy Heinzer]

Why do you believe that creating a new XML backup would incur data loss here?

If you were using an Oracle 11g database on a Jira 7.6 instance, I would argue that you already potentially had data loss possible in that version since that version too does not support that old version of the Oracle database.  Oracle 11 versions have not been supported in Jira since the 6.4 versions.

Ultimately to get past this problem, you are going to need to get your Jira data into a supported database for the version of Jira you want to run here.   I believe that using an XML backup is the best way to do this.

[Mojtaba]

Thanks for the reply, we have done it once before and looks like some add ons data were not migrated, so do you suggest it'll work better than database files  restore to the  new oracle 12 database with native restore? 
will all of the database tables including all of the data related to the addons  be restored with XML restore?

[Andy Heinzer]

In my experience the plugin data is typically still there in the XML backup.  However in situations where you might be doing an upgrade to Jira at the same time, if the plugin isn't loaded into Jira at the time of the import, it's possible that data won't be properly imported.

Which it why I would first suggest that you try using the XML import on your current version of Jira 7.6.x with a new Oracle 12c database or some other supported database type and version for that specific version of Jira.   Once you have this data in a supported database, you can then take steps to upgrade Jira.