Summary

Up to 6.x Jira versions, we may find the xmlvalue column set to CLOB on the Oracle database. It may lead to some failure cases during an upgrade to the newer Jira version. The upgrade tasks will try to fix the database column, resulting in a SQL exception during the execution of the update statement.

Diagnosis

During the execution of the upgrade tasks, you can see in the atlassian-jira.log file a warning message about the entity definition, followed by an error caused by a QueryException.

2020-12-28 12:11:52,446-0500 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] WARNING: Column "XMLVALUE" of table "genericconfiguration" of entity "GenericConfiguration" is of type "CLOB(4000)" in the database, but is defined as type "VARCHAR2(4000)" in the entity definition.
2020-12-28 12:15:35,681-0500 JIRA-Bootstrap ERROR      [c.a.upgrade.core.DefaultUpgradeTaskFactoryProcessor] Upgrade task [host,buildNumber=72001] failed
java.lang.RuntimeException: Error running original upgrade task
	at com.atlassian.jira.upgrade.AbstractUpgradeTask.runUpgrade(AbstractUpgradeTask.java:64)
	at com.atlassian.upgrade.core.DefaultUpgradeTaskFactoryProcessor.runOneUpgradeTask(DefaultUpgradeTaskFactoryProcessor.java:109)
	at com.atlassian.jira.startup.LauncherContextListener.initSlowStuff(LauncherContextListener.java:154)
	at java.lang.Thread.run(Thread.java:748)
    ....
Caused by: com.querydsl.core.QueryException: Caught SQLSyntaxErrorException for update genericconfiguration
set xmlvalue = ?
where genericconfiguration.xmlvalue like ? escape '\' and genericconfiguration.xmlvalue != ?

Environment

• Jira 6.x and 7.x
• Oracle database

Cause

The query that failed is 

update genericconfiguration
set xmlvalue = ?
where genericconfiguration.xmlvalue like ? escape '\' and genericconfiguration.xmlvalue != ?

The use of xmlvalue in the where statement assumes it is varchar, Oracle can't cast that to CLOB on its own.

Furthermore, Oracle doesn't support CLOB in conditions as per this: https://docs.oracle.com/database/121/SQLRF/conditions002.htm#SQLRF52105

Solution

 Before proceeding with the solution steps, we strongly advise performing a Full database backup and engage your Oracle DBA team to help you with it.

1. Create a new VARCHAR(4000) column

   alter table genericconfiguration add VARCHAR2_Column varchar2(4000 CHAR)

2. Copy the data from the xmlvalue column to the new VARCHAR(4000) column

   update genericconfiguration set VARCHAR2_Column = xmlvalue;

3. Do the commit in case the auto-commit is not enabled

   commit;

4. Drop the current xmlvalue column

   alter table genericconfiguration drop column xmlvalue;

5. Rename the VARCHAR(4000) column to xmlvalue


   alter table genericconfiguration rename column VARCHAR2_Column to xmlvalue;

6. After performing those steps, you should be able to complete the upgrade tasks.