What to do with SQLSyntaxException during JIRA "upgrade task"?

user45424

After JIRA Server 7 startup in the logs I see:

 

2017-09-20 16:22:50,809 Caesium-1-2 INFO ServiceRunner [c.a.jira.upgrade.LoggingUpgradeService] run upgrades has started
2017-09-20 16:22:56,692 Caesium-1-2 ERROR ServiceRunner [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:453)
 at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:396)
 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:66)
 at com.atlassian.jira.database.DatabaseAccessorImpl.lambda$runInTransaction$0(DatabaseAccessorImpl.java:98)
 at com.atlassian.jira.database.DatabaseAccessorImpl.executeQuery(DatabaseAccessorImpl.java:67)
 at com.atlassian.jira.database.DatabaseAccessorImpl.runInTransaction(DatabaseAccessorImpl.java:93)
 at com.atlassian.jira.database.DefaultQueryDslAccessor.executeQuery(DefaultQueryDslAccessor.java:65)
 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$2(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.UpgradeScheduler.runUpgrades(UpgradeScheduler.java:52)
 at com.atlassian.scheduler.core.JobLauncher.runJob(JobLauncher.java:153)
 at com.atlassian.scheduler.core.JobLauncher.launchAndBuildResponse(JobLauncher.java:118)
 at com.atlassian.scheduler.core.JobLauncher.launch(JobLauncher.java:97)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.launchJob(CaesiumSchedulerService.java:443)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJob(CaesiumSchedulerService.java:438)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeClusteredJobWithRecoveryGuard(CaesiumSchedulerService.java:462)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService.executeQueuedJob(CaesiumSchedulerService.java:390)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:285)
 at com.atlassian.scheduler.caesium.impl.CaesiumSchedulerService$1.consume(CaesiumSchedulerService.java:282)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeJob(SchedulerQueueWorker.java:65)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.executeNextJob(SchedulerQueueWorker.java:59)
 at com.atlassian.scheduler.caesium.impl.SchedulerQueueWorker.run(SchedulerQueueWorker.java:34)
 at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "HS"."ENTRY_ID": invalid identifier

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
 at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
 at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
 at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:215)
 at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:58)
 at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:943)
 at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1075)
 at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3820)
 at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
 at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1361)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
 at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:98)
 at com.querydsl.sql.dml.SQLInsertClause.execute(SQLInsertClause.java:382)
 ... 43 more
2017-09-20 16:22:56,921 Caesium-1-2 WARN ServiceRunner [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Failed to complete upgrade task [factory=host,buildNumber=73011]
2017-09-20 16:22:56,923 Caesium-1-2 INFO ServiceRunner [c.a.j.upgrade.util.BuildNumberDao] Setting current build number to 73010
2017-09-20 16:22:56,998 Caesium-1-2 ERROR ServiceRunner [c.a.jira.upgrade.LicenseCheckingUpgradeService] There was an error running the upgrades
2017-09-20 16:22:56,998 Caesium-1-2 INFO ServiceRunner [c.a.j.upgrade.util.BuildNumberDao] Setting current version to 7.3.8
2017-09-20 16:22:57,022 ForkJoinPool.commonPool-worker-1 INFO [c.a.j.cache.soy.SoyCacheWarmer] Warmed 41 soy module(s) in 39853 ms, 0 module(s) were not present or disabled, 0 failed.
2017-09-20 16:22:57,033 Caesium-1-4 INFO [c.a.jira.startup.CacheWarmerLauncher] Warmed cache(s) in 39932 ms.
2017-09-20 16:22:57,119 Caesium-1-2 INFO ServiceRunner [c.a.j.upgrade.util.BuildNumberDao] Setting downgrade version to 7.1.2
2017-09-20 16:22:57,210 Caesium-1-2 INFO ServiceRunner [c.a.jira.upgrade.LoggingUpgradeService] run upgrades has finished unsuccessfully, and took 6401 milliseconds to process.
2017-09-20 16:22:57,210 Caesium-1-2 INFO ServiceRunner [c.a.jira.upgrade.UpgradeScheduler] Scheduled upgrades completed with an error

 

How big of a problem this is? What is the original cause: wrong DB mappings somewhere in JIRA config or what? What is best way to make this error go away?

somethingblue

Hi,

Hopefully you've been able to sort this by now, however, if not I want to see if I can help provide some guidance.  The error you're seeing is the Oracle ORA-00942 error as well as an error that upgrade tasks failed.

We have a knowledge base article titled How to Fix Oracle Error ORA-00942: table or view does not exist that may be of use to you:

As from the error command:

The table or view entered does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. Existing user tables and views can be listed by querying the data dictionary. Certain privileges may be required to access the table. If an application returned this message, the table the application tried to access does not exist in the database, or the application does not have access to it.

This is most commonly caused by an improperly configured database connection.  One thing to keep in mind is that the permissions on an Oracle database need to be setup exactly as the permissions state in the Connecting JIRA to Oracle knowledge base article.

To resolve the issue:

• Confirm that the database connection has been correctly configured, as in our Connecting JIRA to Oracle documentation, ensuring both the appropriate permissions and required unlimited quota is granted to the JIRA application database user.

• This DBA Oracle article has further information on this error. If you're experiencing further problems, please ensure to raise this with your Oracle DBA.

Cheers,

Branden