JIRA Upgrade fails due to incorrect syntax near 'offset'

[Adrian Wheat]

Hi,

I have just attempted to upgrade to the latest version of JIRA. 

The external database resides on Sql Server 2008.

I get the following error details appear in the log file and the upgrade fails. The error appears to be caused by one of the upgrade steps using a SQL statement that is invalid for SQL Server 2008. It uses the OFFSET clause, which was not introduced until SQL Server 2012.

Is there a way to workaround this?

LOG FILE CONTENTS

************************

2017-10-25 11:12:18,469 JIRA-Bootstrap INFO [c.a.jira.upgrade.LoggingUpgradeService] run upgrades has started
2017-10-25 11:12:18,578 JIRA-Bootstrap ERROR [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=70024] failed
java.lang.RuntimeException: Error running original upgrade task
at com.atlassian.jira.upgrade.AbstractUpgradeTask.runUpgrade(AbstractUpgradeTask.java:63)
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:87)
at com.atlassian.jira.startup.ActiveServicesLauncher.start(ActiveServicesLauncher.java:50)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDBActivated(DefaultJiraLauncher.java:181)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$postDbLaunch$2(DefaultJiraLauncher.java:155)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrEnqueue(DatabaseConfigurationManagerImpl.java:304)
at com.atlassian.jira.config.database.DatabaseConfigurationManagerImpl.doNowOrWhenDatabaseActivated(DatabaseConfigurationManagerImpl.java:199)
at com.atlassian.jira.startup.DefaultJiraLauncher.postDbLaunch(DefaultJiraLauncher.java:146)
at com.atlassian.jira.startup.DefaultJiraLauncher.lambda$start$0(DefaultJiraLauncher.java:105)
at com.atlassian.jira.util.devspeed.JiraDevSpeedTimer.run(JiraDevSpeedTimer.java:31)
at com.atlassian.jira.startup.DefaultJiraLauncher.start(DefaultJiraLauncher.java:103)
at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:157)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.querydsl.core.QueryException: Caught SQLServerException for select AVATAR.id
from dbo.avatar AVATAR
where AVATAR.avatartype = ? and AVATAR.systemavatar = ? and AVATAR.filename = ?
order by AVATAR.id asc
offset ? rows fetch next ? rows only
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:410)
at com.querydsl.sql.AbstractSQLQuery.iterate(AbstractSQLQuery.java:342)
at com.querydsl.sql.ProjectableSQLQuery.fetchOne(ProjectableSQLQuery.java:397)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build70024.prepareFallBackIconForIssueType(UpgradeTask_Build70024.java:270)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build70024.lambda$upgradeIssueTypeIcons$0(UpgradeTask_Build70024.java:146)
at com.atlassian.jira.database.DbConnectionManagerImpl.execute(DbConnectionManagerImpl.java:73)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build70024.upgradeIssueTypeIcons(UpgradeTask_Build70024.java:142)
at com.atlassian.jira.upgrade.tasks.UpgradeTask_Build70024.doUpgrade(UpgradeTask_Build70024.java:136)
at com.atlassian.jira.upgrade.AbstractUpgradeTask.runUpgrade(AbstractUpgradeTask.java:61)
... 35 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'offset'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:258)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1535)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:467)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:409)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7151)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:219)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:199)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:331)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
at com.querydsl.sql.AbstractSQLQuery.iterateSingle(AbstractSQLQuery.java:370)
... 43 more
2017-10-25 11:12:18,618 JIRA-Bootstrap WARN [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Failed to complete upgrade task [factory=host,buildNumber=70024]
2017-10-25 11:12:18,619 JIRA-Bootstrap INFO [c.a.j.upgrade.util.BuildNumberDao] Setting current build number to 70023
2017-10-25 11:12:18,623 JIRA-Bootstrap ERROR [c.a.jira.upgrade.LicenseCheckingUpgradeService] There was an error running the upgrades
2017-10-25 11:12:18,623 JIRA-Bootstrap INFO [c.a.j.upgrade.util.BuildNumberDao] Setting current version to 7.5.1
2017-10-25 11:12:18,626 JIRA-Bootstrap INFO [c.a.j.upgrade.util.BuildNumberDao] Setting downgrade version to 7.1.2
2017-10-25 11:12:18,628 JIRA-Bootstrap INFO [c.a.jira.upgrade.LoggingUpgradeService] run upgrades has finished unsuccessfully, and took 159 milliseconds to process.
2017-10-25 11:12:18,633 JIRA-Bootstrap INFO [c.a.jira.scheduler.JiraSchedulerLauncher] JIRA Scheduler not started: JIRA startup failed.

[Nic Brough -Adaptavist- · Accepted solution]

Jira version 7.5 does not work with MS SQL Server 2008 - you need to be using 2012 or 2014

[Adrian Wheat]

Thanks Nic,

I belatedly realised this after posting. I've installed 7.1 instead and all is fine.