Database Upgrade Error: column to be renamed is used in a virtual column expression
プラットフォームについて: サーバーと Data Center のみ。この記事は、サーバーおよび Data Center プラットフォームのアトラシアン製品にのみ適用されます。
When upgrading Bitbucket Server, an error like the following appears
liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE STASH.sta_pr_participant RENAME COLUMN pr_approved TO participant_status: ORA-54032: column to be renamed is used in a virtual column expression
- Oracle (any version)
- Bitbucket Server 4.2+ (4.2 is known to include a rename column operation during the upgrade)
Run the following SQL query in your database using the table name mentioned in the error message. For example, in the error message shown in this article, the table name is
STA_PR_PARTICIPANT. Note that whilst the table name appears in lower case in the error message, it may be upper case in your DB. This query is case sensitive so if you receive no results, check whether the table name is upper case inside your database.
SELECT COLUMN_NAME, DATA_DEFAULT, HIDDEN_COLUMN FROM USER_TAB_COLS WHERE TABLE_NAME = 'STA_PR_PARTICIPANT';
The output of this query should look something like this:
(null) いいえ USER_ID (null) いいえ PR_ROLE (null) いいえ PR_ID (null) いいえ ID (null) いいえ SYS_STS4YT35CFC$9LH4KD69RTZPF3 SYS_OP_COMBINED_HASH("PR_ROLE","USER_ID","PR_APPROVED") YES
If you see an entry where
YESthen this most likely indicates the presence of a virtual column, the presence of which is blocking the upgrade process.
Oracle databases supports the use of virtual columns. If a virtual column has been created that references a regular database column, the Oracle database engine will prevent Bitbucket Server from renaming the regular column and cause the database upgrade to fail.
Virtual columns most commonly exist when Extended Statistics has been enabled for your database, and will look very similar to the above example. Extended Statistics is a feature which stores additional optimisation data about your table inside virtual columns, and the presence of these prevents regular columns from being renamed. The Oracle blog post ORA-54033 and the Hidden Virtual Column Mystery goes into more detail about this issue.
Note that virtual columns are never created by Bitbucket Server. Bitbucket Server accesses your database via a vendor-agnostic ORM layer and therefore does not make use of RDBMS-specific features like virtual columns.
Remove Extended Statistics
Before proceeding, make sure the Bitbucket Server process is not running. If Extended Statistics has been enabled, contact your database administrator to have them drop the Extended Statistics metadata from the table, and proceed with your upgrade. If you wish to enable Extended Statistics again after the upgrade you may do so, however be aware that you may need to repeat this process again for subsequent upgrades otherwise you risk running into this issue again.
データベースの変更を行う場合は必ず事前に可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。
Removing columns created by Extended Statistics requires using an in-build stored procedure,
DBMS_STATS.DROP_EXTENDED_STATS(). Usage of this stored procedure is covered further in ORA-54033 and the Hidden Virtual Column Mystery, and looks similar to the following:
EXEC DBMS_STATS.DROP_EXTENDED_STATS(ownname=>'<YOUR_DB_USERNAME>', tabname=>'STA_PR_PARTICIPANT', extension=>'("PR_ROLE", "USER_ID", "PR_APPROVED")')
Note that in the above query, the value of the
extension parameter is contained in
SYS_OP_COMBINED_HASH() from the previous query.
Remove the virtual column
It is also possible that Virtual Columns may have been created by some other system or process. If you are not sure of the origin of the virtual columns in your database, contact your database administrator to investigate further and to have these removed before proceeding with your upgrade.