Upgrading to Confluence 5.9.x or higher version fails with error 'failed during the SCHEMA_UPGRADE phase due to: null'

Summary

Problem

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

This is also documented in this bug report: CONFSERVER-43759 - Upgrade fail when FK9228242D16994414 is missing from the spaces table

Diagnosis

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:

   1 show create table spaces; 

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

Cause

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

The task tries to drop the

FK9228242D16994414

constraint, however as it doesn't exist, the returned message is the null pointer error thrown in the Confluence logs above.

Solution

Workaround

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

1. Verify the SPACES table includes the new constraint

   1 show create table spaces; 

   For Oracle database you need to run this:

   1. add constraint to SPACES table:

      1 2 3 4 ALTER TABLE SPACES ADD CONSTRAINT FK9228242D16994414 FOREIGN KEY (SPACEGROUPID) REFERENCES SPACEGROUPS (SPACEGROUPID);

   2. check if the constraint was applied:

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