Upgrade task buildNumber=73011 failed com.querydsl.core.QueryException: Caught SQLSyntaxErrorException

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

 

 

Problem

When upgrading Jira to a 7.x version or higher, this error can occur in the logs.   You will be able to find this kind of exception in the atlassian-jira.log file:

2017-09-08 12:01:40,369 Caesium-1-3 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: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)

 

 

 

Diagnosis

Environment

  • In the atlassian-jira.log file you should be able to see what specific version of database you are using


2017-09-08 11:57:21,442 JIRA-Bootstrap INFO      [c.a.jira.startup.JiraStartupLogger] 
    
    Database configuration OK
    
    ___ Database Configuration _________________
    
         Loading entityengine.xml from                 : file:/opt/atlassian/jira/int/atlassian-jira/WEB-INF/classes/entityengine.xml
         Entity model field type name                  : oracle10g
         Entity model schema name                      : 
         Database Version                              : Oracle - Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
                                                         With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Note: the entity model number is expected to be 'oracle10g', however the "Database Version" listed there indicates that this instance is using an 11.x version of Oracle.

 

Cause

When you see this specific error message that references the use of an Oracle database, but not an Oracle 12c database.  Jira 7 does support Oracle as a database platform, but only the 12c versions as per Supported platforms - Administering Jira Server Applications 7.1 - Atlassian Documentation. 

 

Resolution

To resolve this issue, the Jira data should be migrated to a supported database.

  1. Select a supported database from the Supported platforms - Atlassian Documentation (Note that different versions of Jira support different database versions.  It is important to pick the version that you are upgrading to.)
  2. Create an XML backup of your existing Jira data.  You should have one of these from before the upgrade attempt.  If not, then by default Jira is automatically creating these backups and storing them to $JIRAHOME/export/
  3. Follow the corresponding guide in Connecting Jira to a database. With this guide you can make sure to correctly setup and configure this database for Jira.
  4. You then need to make sure that your $JIRAHOME/dbconfig.xml is setup to connect to the new empty database.   You can either follow the guide in the database setup, or use the Jira Config tool to set this.

  5. Restart Jira.   When starts up connected to an empty database, it automatically launches the setup wizard.
  6. In the setup wizard, on the first page is a link to "Import existing data" click this
  7. From this next page you can then point to the location of your XML backup zip to restore.  Jira is expecting this file to be stored in the $JIRAHOME/import/ folder if you do not define a specific path to this file.

 

These steps are also outlined in Switching Databases.

 

Last modified on Nov 2, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.