Problem

When upgrading Confluence, the upgrade fails due to Cannot add or update a child row: a foreign key constraint fails

The following appears in the atlassian-confluence.log:

2014-08-07 13:09:31,286 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate]
execute Unsuccessful: alter table CONTENT add constraint FK6382C059B2DC6081
foreign key (SPACEID) references SPACES (SPACEID)
2014-08-07 13:09:31,287 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate]
execute Cannot add or update a child row: a foreign key constraint fails
(`j2ee_help_test2`.<result 2 when explaining filename '#sql-77de_a'>,
CONSTRAINT `FK6382C059B2DC6081` FOREIGN KEY (`SPACEID`) REFERENCES `SPACES`
(`SPACEID`))
2014-08-07 13:09:31,288 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate]
execute could not complete schema update
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Cannot add or update a child row: a foreign key constraint fails
(`j2ee_help_test2`.<result 2 when explaining filename '#sql-77de_a'>,
CONSTRAINT `FK6382C059B2DC6081` FOREIGN KEY (`SPACEID`) REFERENCES `SPACES`
(`SPACEID`))
2014-08-07 13:09:31,308 ERROR [main]
[atlassian.confluence.upgrade.UpgradeLauncherServletContextListener]
contextInitialized Upgrade failed, application will not start:
com.atlassian.config.ConfigurationException: Cannot update schema
com.atlassian.confluence.upgrade.UpgradeException:
com.atlassian.config.ConfigurationException: Cannot update schema 

Diagnosis

To determine if this is the appropriate fix, run the following SQL query:

SELECT c.SPACEID
FROM CONTENT c
LEFT JOIN SPACES s 
ON c.SPACEID=s.SPACEID
WHERE c.SPACEID IS NOT NULL and s.SPACEID IS NULL;

If there are any results then you are affected by this issue.

Cause

Because the rows that will be removed from content are invalid, the rows referencing those in all tables are also therefore invalid. We need to clean up these tables first. To do this you'll need to identify all the invalid contentid values. To identify the affected rows run the following SQL statement and replace it with the values in the table that follows:

Resolution

1. Run the following with the value pairs in the above table:
   

   SELECT * FROM <table_name> WHERE <column_name> IN (SELECT c.CONTENTID FROM CONTENT c LEFT JOIN SPACES s ON c.SPACEID = s.SPACEID WHERE c.SPACEID IS NOT NULL and s.SPACEID IS NULL);

2. Once you have identified that rows need to be removed, use the following SQL statement to remove them (again running for each value pair in the table above):
   

   DELETE FROM <table_name> WHERE <column_name> IN (SELECT c.CONTENTID FROM CONTENT c LEFT JOIN SPACES s ON c.SPACEID = s.SPACEID WHERE c.SPACEID IS NOT NULL and s.SPACEID IS NULL);

3. Once all of the affected rows have been removed from the above tables, use the following to clean up the content table:

   DELETE FROM <table_name> WHERE <constraint_name> IN (SELECT c.CONTENTID FROM CONTENT c LEFT JOIN SPACES s ON c.SPACEID = s.SPACEID WHERE c.SPACEID IS NOT NULL and s.SPACEID IS NULL);

4. Once all of the affected rows have been removed from the above tables, use the following to clean up the content table:

   • These first two will create a table with all of the values in content where the corresponding value in spaces is null.

     CREATE TABLE foo (deletevalues int);

     INSERT INTO foo 
     SELECT c.SPACEID
     FROM CONTENT c
     LEFT JOIN SPACES s 
     ON c.SPACEID = s.SPACEID
     WHERE c.SPACEID IS NOT NULL and s.SPACEID IS NULL;

   • This removes those values from content, at this point the upgrade process should not experience the error it has been getting.

     DELETE FROM CONTENT 
     WHERE SPACEID in (SELECT deletevalues FROM foo);

     DROP TABLE foo;

5. Now, you should be able to perform the upgrade as expected.