Database Upgrade Error: column to be renamed is used in a virtual column expression
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
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
Environment
Oracle (any version)
Bitbucket Server 4.2+ (4.2 is known to include a rename column operation during the upgrade)
Diagnosis
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.
SQL query
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:
COLUMN_NAME | DATA_DEFAULT | HIDDEN_COLUMN |
PR_APPROVED | (null) | NO |
USER_ID | (null) | NO |
PR_ROLE | (null) | NO |
PR_ID | (null) | NO |
ID | (null) | NO |
SYS_STS4YT35CFC$9LH4KD69RTZPF3 | SYS_OP_COMBINED_HASH("PR_ROLE","USER_ID","PR_APPROVED") | YES |
If you see an entry where HIDDEN_COLUMN is YES, then this most likely indicates the presence of a virtual column, the presence of which is blocking the upgrade process.
Cause
Oracle databases support 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.
Solution
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.
Back up your data before database modifications
Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
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:
|
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.
Was this helpful?