Database Upgrade Error: column to be renamed is used in a virtual column expression

Still need help?

The Atlassian Community is here for you.

Ask the community

This Knowledge Base article was written specifically for the Atlassian Server platform. Due to the Restricted functions in Atlassian Cloud apps, the contents of this article cannot be applied to Atlassian Cloud applications.

Problem

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

Diagnosis

Environment

  • Oracle (any version)
  • Bitbucket Server 4.2+ (4.2 is known to include a rename column operation during the upgrade)

Diagnostic Steps

  • 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:

    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 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.

Resolution

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. 

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: 

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.

Last modified on May 31, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.