During Confluence upgrade, foreign key constraint fails with "...SPACEID references SPACES..."

Still need help?

The Atlassian Community is here for you.

Ask the community

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

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.


Table Name

Column Name

likes

contentid

links

contentid

attachments

pageid

content

prevver

content

parentid

content

pageid

content

parentcommentid

content

parentccid

spaces

homepage

spaces

spacedescid

confancestors

ancestorid

confancestors

descendentid

extrnlnks

contentid

contentproperties

contentid

notifications

contentid

bodycontent

contentid

content_perm_set

content_id

content_label

contentid

trackbacklinks

contentid


  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.

Last modified on Jun 20, 2019

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.