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

お困りですか?

アトラシアン コミュニティをご利用ください。

コミュニティに質問

問題

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

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 

診断

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.

原因

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:

ソリューション

データベースの変更を行う場合は必ず事前にバックアップを取得してください。


Table Name

列名

いいね!

contentid

links

contentid

attachments

pageid

content

prevver

content

parentid

content

pageid

content

parentcommentid

content

parentccid

スペース

homepage

スペース

spacedescid

confancestors

ancestorid

confancestors

descendentid

extrnlnks

contentid

contentproperties

contentid

通知

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.

最終更新日 2019 年 6 月 20 日

この内容はお役に立ちましたか?

はい
いいえ
この記事についてのフィードバックを送信する
Powered by Confluence and Scroll Viewport.