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. 

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.