Upgrading from 5.9.x to 5.10.x or above fails if any data exists in user_relation, content_relation or usercontent_relation tables

Summary

When upgrading to 5.10 or above from 5.9.x, the RelationConstraintsPreSchemaUpgradeTask gets re-run due to the build number being bumped since 5.10. This means that if any data exists in any of the affected tables user_relation, content_relation or usercontent_relation, then the upgrade task will fail, and hence the upgrade as a whole.

Steps to Reproduce

1. Install Confluence 5.9.x
2. Perform some action which causes data to exist in these tables (I am not actually sure how it gets there)
3. Upgrade to 5.10 or above

Expected Results

Upgrade succeeds

Actual Results

Upgrade fails with the below log entry:

2017-01-27 17:24:59,240 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: RelationService upgrade failed to prevent data loss. Please read https://confluence.atlassian.com/x/MRJZLw for more details
com.atlassian.confluence.upgrade.UpgradeException: RelationService upgrade failed to prevent data loss. Please read https://confluence.atlassian.com/x/MRJZLw for more details
        at com.atlassian.confluence.upgrade.upgradetask.RelationConstraintsPreSchemaUpgradeTask.assertTableIsEmpty(RelationConstraintsPreSchemaUpgradeTask.java:72)
        at com.atlassian.confluence.upgrade.upgradetask.RelationConstraintsPreSchemaUpgradeTask.upgradeTable(RelationConstraintsPreSchemaUpgradeTask.java:64)
        at com.atlassian.confluence.upgrade.upgradetask.RelationConstraintsPreSchemaUpgradeTask.doUpgrade(RelationConstraintsPreSchemaUpgradeTask.java:58)
        at com.atlassian.confluence.upgrade.AbstractUpgradeManager$UpgradeStep$3.execute(AbstractUpgradeManager.java:641)
        at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeTask(AbstractUpgradeManager.java:248)
        at com.atlassian.confluence.upgrade.AbstractUpgradeManager.executeUpgradeStep(AbstractUpgradeManager.java:228)
        at com.atlassian.confluence.upgrade.AbstractUpgradeManager.runSchemaUpgradeTasks(AbstractUpgradeManager.java:190)

Workaround

The workaround is to store the data in a temporary table while upgrading, and then copy it back after it's complete. Here's some steps to perform this. The examples below are for MySQL and Postgres environments, but the same concept can be adapted to other DBMS though the syntax may be different:

MySQL

1. Stop Confluence and ensure you take a database backup in case anything goes wrong
2. Create a new temporary table which is a copy of the table containing data, for example:

   create table usercontent_relation_backup like usercontent_relation;
   insert usercontent_relation_backup select * from usercontent_relation;
   

3. Truncate the original table, for example:

   truncate table usercontent_relation;
   

4. Perform the upgrade as normal
5. Stop Confluence
6. Move the data back from the temporary table into the newly created empty table, for example:

   insert usercontent_relation select * from usercontent_relation_backup;
   

7. Remove the temporary table:

   drop table usercontent_relation_backup;
   

8. Start Confluence again

Postgres

1. Stop Confluence and ensure you take a database backup in case anything goes wrong
2. Create a new temporary table which is a copy of the table containing data, for example:

   CREATE TABLE usercontent_relation_backup AS SELECT * FROM usercontent_relation;
   

3. Truncate the original table, for example:

   TRUNCATE usercontent_relation;
   

4. Perform the upgrade as normal
5. Stop Confluence
6. Move the data back from the temporary table into the newly created empty table, for example:

   INSERT INTO usercontent_relation SELECT * FROM usercontent_relation_backup;
   

7. Remove the temporary table:

   DROP TABLE usercontent_relation_backup;
   

8. Start Confluence again

Oracle

1. Stop Confluence and ensure you take a database backup in case anything goes wrong
2. Create a new temporary table which is a copy of the table containing data, for example:

   CREATE TABLE usercontent_relation_backup AS SELECT * FROM usercontent_relation; 

   Per https://stackoverflow.com/questions/8997399/creating-duplicate-table-from-existing-table

3. Truncate the original table, for example:

   TRUNCATE TABLE usercontent_relation; 

   Per https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10007.htm#SQLRF01707

4. Perform the upgrade as normal
5. Stop Confluence
6. Move the data back from the temporary table into the newly created empty table, for example:

   INSERT INTO usercontent_relation SELECT * FROM usercontent_relation_backup; 

   Per https://www.techonthenet.com/oracle/insert.php

7. Remove the temporary table

   DROP TABLE usercontent_relation_backup PURGE; 

   Per https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

8. Start Confluence again.