問題

Upgrading to Confluence to 5.9.x and above fails with the following error message:

 INFO [localhost-startStop-1] [confluence.upgrade.upgradetask.DropSpaceGroupTablesUpgradeTask] doUpgrade Starting dropping unused column SPACES.SPACEGROUPID, table SPACEGROUPPERMISSIONS, and table SPACEGROUPS
 ERROR [localhost-startStop-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DropSpaceGroupTablesUpgradeTask@535b5afa failed during the SCHEMA_UPGRADE phase due to: null
com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DropSpaceGroupTablesUpgradeTask@535b5afa failed during the SCHEMA_UPGRADE phase due to: null

This is also documented in this bug report:  CONFSERVER-43759 - Getting issue details... STATUS

診断

Take the following steps to confirm if you are being affected by this issue:

1. While on Confluence 5.8.x or older, run this SQL query in MySQL to show the structure for the SPACES table:

   show create table spaces; 

2. If FK9228242D16994414 is not present in the table constraints, you are affected by this issue.

原因

There is a missing foreign key constraint in the table SPACES that refers to the SPACEGROUPS table. Confluence 5.9.x no longer includes the table SPACEGROUPS, which should be dropped, but when the upgrade task that performs this is run, it fails due to the missing foreign key constraint.

This upgrade task contains a specific list of activities to perform in sequence:

1. drop the foreign key SPACEGROUPID from SPACES
2. drop the index s_spacegroupid_idx from SPACES
3. drop the column SPACES.SPACEGROUPID
4. drop the table SPACEGROUPPERMISSIONS
5. drop the table SPACEGROUPS

回避策

Run this SQL command to recreate the constraint on the table Spaces:

ALTER TABLE 'SPACES' ADD CONSTRAINT 'FK9228242D16994414' FOREIGN KEY ('SPACEGROUPID') REFERENCES 'SPACEGROUPS' ('SPACEGROUPID');

1. Verify the SPACES table includes the new constraint

   show create table spaces; 

   For Oracle database you need to run this:

   1. add constraint to SPACES table:

      ALTER TABLE
        SPACES
      ADD
        CONSTRAINT FK9228242D16994414 FOREIGN KEY (SPACEGROUPID) REFERENCES SPACEGROUPS (SPACEGROUPID);

   2. check if the constraint was applied:

   select * from user_cons_columns where table_name='SPACES' and constraint_name='FK9228242D16994414';