Column "XMLVALUE" of table "genericconfiguration" of entity "GenericConfiguration" is of type "CLOB(4000) in the database
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
要約
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.
診断
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 != ?
環境
- Jira 6.x and 7.x
- Oracle データベース
原因
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
ソリューション
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
alter table genericconfiguration add VARCHAR2_Column varchar2(4000 CHAR)
Copy the data from the
xmlvalue
column to the new VARCHAR(4000) columnupdate genericconfiguration set VARCHAR2_Column = xmlvalue;
Do the commit in case the auto-commit is not enabled
commit;
Drop the current
xmlvalue
columnalter table genericconfiguration drop column xmlvalue;
Rename the VARCHAR(4000) column to
xmlvalue
alter table genericconfiguration rename column VARCHAR2_Column to xmlvalue;
After performing those steps, you should be able to complete the upgrade tasks.