Column "XMLVALUE" of table "genericconfiguration" of entity "GenericConfiguration" is of type "CLOB(4000) in the database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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
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.
1
2
3
4
5
6
7
8
9
10
11
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
1
2
3
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.
Create a new VARCHAR(4000) column
1alter table genericconfiguration add VARCHAR2_Column varchar2(4000 CHAR)Copy the data from the
xmlvaluecolumn to the new VARCHAR(4000) column1update genericconfiguration set VARCHAR2_Column = xmlvalue;Do the commit in case the auto-commit is not enabled
1commit;Drop the current
xmlvaluecolumn1alter table genericconfiguration drop column xmlvalue;Rename the VARCHAR(4000) column to
xmlvalue1alter table genericconfiguration rename column VARCHAR2_Column to xmlvalue;After performing those steps, you should be able to complete the upgrade tasks.
Was this helpful?