Issue Summary

A Confluence instance with an Oracle or MSSQL DB backend fails to upgrade to listed affected Confluence versions if the label table has multiple rows of NULL names and NULL owners

The upgrade will fail at executing schema modification operations triggered by the LabelUniqueIndexUpgradeTask, specifically the addition of a database-index named IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on that table.

Note: Postgres and MySQL instances will let the upgrade succeed even in the presence of multiple NULL valued label-names and label-owners since NULL values are parsed/processed differently on those databases.

Steps to Reproduce

On a Confluence 7.19.x or 8.5.x instance (with Oracle or MSSQL as the backend):
Add bogus NULL value data to the label table like this (problematic values highlighted in red):

To check if the instance already has this kind of data, execute the following SQL:

SELECT NAME, OWNER, NAMESPACE, COUNT(*)
FROM LABEL
WHERE NAME IS NULL AND OWNER IS NULL 
GROUP BY NAME, OWNER, NAMESPACE
HAVING COUNT(*) > 1;

With this data, attempt upgrading the Confluence instance to listed affected versions.

Expected Results

The upgrade task should deal with duplicates in the label table by deleting/merging the relevant records.
It should then successfully add the IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE index to that table.

Actual Results

While the upgrade task successfully deletes non-NULL duplicates, it leaves the NULL named, NULL owned rows untouched.
Since those values remain undeleted, the subsequent attempt at adding the IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE index to that table fails with an ORA-01452 error.
Given below is the log-sequence to upgrade failure (Oracle example, DEBUG logging enabled for com.atlassian.confluence.upgrade to show additional info):

1. processing of duplicate labels begins and ends:

   2025-03-11 17:47:49,878 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade LabelUniqueIndexUpgradeTask: starting upgrade task
   2025-03-11 17:47:49,878 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade Starting cleaning up duplicated labels
   2025-03-11 17:47:49,927 INFO [Catalina-utility-1] [persistence.hibernate.schema.LabelUniqueIndexHelper] lambda$cleanUpDuplicatedLabels$0 Looking for duplicate label records.
   2025-03-11 17:47:49,949 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade Finished cleaning up duplicated labels
   

   
   
   

2. addition of a new virtual column starts and finishes:

   2025-03-11 17:47:50,018 INFO [Catalina-utility-1] [persistence.hibernate.schema.LabelUniqueIndexHelper] getAddVirtualColumnCommand LABEL table OWNER column type is: NVARCHAR2(255), will create virtual column with the same type.
   2025-03-11 17:47:50,018 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateAlterTableExecutor] alterTable Executing grouped DDL commands on [LABEL]
   2025-03-11 17:47:50,019 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateDdlExecutor] executeDdlStatements Executing DDL: alter table LABEL add  (OWNER_NOT_NULL NVARCHAR2(255) GENERATED ALWAYS AS (COALESCE(OWNER, CAST('' AS NVARCHAR2(255)))) VIRTUAL)
   2025-03-11 17:47:50,031 INFO [Catalina-utility-1] [persistence.hibernate.schema.LabelUniqueIndexHelper] lambda$createVirtualColumnAndUniqueIndex$1 Finished adding of virtual column to LABEL table.
   

   
   
   

3. attempt to add a new database-index starts and then fails, also causing upgrade failure:

   2025-03-11 17:47:50,031 INFO [Catalina-utility-1] [persistence.hibernate.schema.LabelUniqueIndexHelper] lambda$createVirtualColumnAndUniqueIndex$1 Starting adding of unique index to LABEL table.
   2025-03-11 17:47:50,031 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateDdlExecutor] executeDdlStatements Executing DDL: create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)
   ...
   ...
   2025-03-11 17:47:50,064 WARN [Catalina-utility-1] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
       [com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask.doUpgrade]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #135461760)
   2025-03-11 17:47:50,093 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@22938455 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [72000]; error code [1452]; ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   ...
   ...
   com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@22938455 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [72000]; error code [1452]; ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   ...
   ...
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:302)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:258)
   ...
   ...
   Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [72000]; error code [1452]; ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   ...
   ...
   Caused by: java.sql.SQLException: ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   ...
   ...
   Caused by: Error : 1452, Position : 71, Sql = create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE), OriginalSql = create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE), Error Msg = ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   ...
   ...
   2025-03-11 17:47:50,095 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
   2025-03-11 17:47:50,096 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [72000]; error code [1452]; ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
   

Here's a similar upgrade failure example for MSSQL:

1. processing of duplicate labels begins and ends, addition of a new virtual column starts and finishes:

   2025-03-12 06:18:59,272 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade LabelUniqueIndexUpgradeTask: starting upgrade task
   2025-03-12 06:18:59,272 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade Starting cleaning up duplicated labels
   2025-03-12 06:18:59,335 INFO [Catalina-utility-1] [confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask] doUpgrade Finished cleaning up duplicated labels
   2025-03-12 06:18:59,335 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateAlterTableExecutor] alterTable Executing grouped DDL commands on [LABEL]
   2025-03-12 06:18:59,335 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateDdlExecutor] executeDdlStatements Executing DDL: alter table LABEL add  OWNER_NOT_NULL AS (COALESCE(OWNER, ''))
   

   
   
   

2. attempt to add a new database-index starts and then fails, also causing upgrade failure:

   2025-03-12 06:18:59,335 INFO [Catalina-utility-1] [confluence.upgrade.ddl.HibernateDdlExecutor] executeDdlStatements Executing DDL: create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)
   2025-03-12 06:18:59,367 WARN [Catalina-utility-1] [confluence.impl.hibernate.ConfluenceHibernateTransactionManager] doRollback Performing rollback. Transactions:
       [com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask.doUpgrade]: PROPAGATION_REQUIRES_NEW,ISOLATION_DEFAULT (Session #1862293878)
   ...
   ...	
   2025-03-12 06:18:59,382 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@314785e4 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (<NULL>, , global).
   com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@314785e4 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (<NULL>, , global).
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:302)
   	at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:258)
   ...
   ...
   Caused by: org.springframework.jdbc.UncategorizedSQLException: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (<NULL>, , global).
   ...
   ...
   Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (<NULL>, , global).
   ...
   ...
   2025-03-12 06:18:59,382 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1 errors were encountered during upgrade:
   2025-03-12 06:18:59,382 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades 1: StatementCallback; uncategorized SQLException for SQL [create unique  index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (<NULL>, , global).
   

While the logs show that, the Confluence web-interface will show a "Confluence had problems starting up" johnson (startup error) screen with the following message:
Oracle

Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@22938455 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [72000]; error code [1452]; ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

MSSQL

Upgrade failed. Please consult the system logs for details. You will need to fix these problems, restore your database and confluence home directory to the pre upgrade state. Then retry the upgrade. Upgrade error message: Upgrade task com.atlassian.confluence.upgrade.upgradetask.LabelUniqueIndexUpgradeTask@314785e4 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; uncategorized SQLException for SQL [create unique index IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE on LABEL (NAME, OWNER_NOT_NULL, NAMESPACE)]; SQL state [S0001]; error code [1505]; The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.LABEL' and the index name 'IDX_UNIQUE_LABEL_NAME_OWNER_NOT_NULL_NAMESPACE'. The duplicate key value is (, , global).

Workaround

NULL valued label-names and label-owners represent invalid/corrupted data, and they (along with their references) should be removed from the database.

Rollback the affected instance to the last stable pre-upgrade state/snapshot (i.e. Confluence Home, Shared Home and Install directories, as well as the DB).
With Confluence now restored to the previous version snapshot, stop Confluence (if running), ensure that suitable database backups have been taken, and then execute the following SQL:

1. To find all rows in the label table with NULL valued label-names and label-owners, execute the following SQL:
   Query 1 - get all duplicated NULL-data

   SELECT * FROM LABEL WHERE NAME IS NULL AND OWNER IS NULL ;
   

   Note the labelid values here.
   
   

2. Using those labelid values, first delete any references in the content_label and, then the notifications tables:
   Query 2 - to delete references from the content_label table

   DELETE FROM CONTENT_LABEL WHERE LABELID IN (SELECT LABELID FROM LABEL WHERE NAME IS NULL AND OWNER IS NULL);
   

   Query 3 - to delete references from the notifications table

   DELETE FROM NOTIFICATIONS WHERE LABELID IN (SELECT LABELID FROM LABEL WHERE NAME IS NULL AND OWNER IS NULL);
   

   
   
   

3. With the foreign/child references thus deleted, the NULL value duplicates in the label table can then be deleted safely:
   Query 4 - to delete duplicates in the label table

   DELETE FROM LABEL WHERE LABELID IN (SELECT LABELID FROM LABEL WHERE NAME IS NULL AND OWNER IS NULL);
   

   note: all of the other non-NULL duplicate data will be addressed by the code in the upgrade task itself, manual-intervention is not necessary