Upgrade fails due to a constraint violation on BODYCONTENT
問題
The upgrade attempt fails. The following appears in the atlassian-confluence.log:
2009-06-04 15:37:58,385 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT (CONTENTID)
2009-06-04 15:37:58,388 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Cannot add or update a child row: a foreign key constraint fails (`confluencedb/#sql-650f_c3`, CONSTRAINT `FKA898D4778DD41734` FOREIGN KEY (`CONTENTID`) REFERENCES `content` (`CONTENTID`))
...
Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`confluencedb/#sql-650f_c3`, CONSTRAINT `FKA898D4778DD41734` FOREIGN KEY (`CONTENTID`) REFERENCES `content` (`CONTENTID`))
...
2009-06-04 15:37:58,411 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized 1 errors were encountered during upgrade:
2009-06-04 15:37:58,412 ERROR [main] [atlassian.confluence.upgrade.UpgradeLauncherServletContextListener] contextInitialized 1: Cannot update schema
診断
データベースに対して次のクエリを実行します。
select BODYCONTENT.CONTENTID from BODYCONTENT left join CONTENT on BODYCONTENT.CONTENTID=CONTENT.CONTENTID where BODYCONTENT.CONTENTID is not null and CONTENT.CONTENTID is null;
This should return 0 values. If it does, then this Knowledgebase article does not adequeately describe your problem. Do not perform the resolution steps if this is the case.
原因
Database Integrity: For some reason, some bad data crept into the instance. If you take a look at the Confluence Data Model, the content bodies of pages and news and such ( the content entries are in the CONTENT table) are stored in the BODYCONTENT table.
In Confluence 3.0, we enforce a constraint that establishes that there is a 1 to 1 relationship to between content entries and content bodies, and for some reason, there exist bodycontent rows that don't refer to valid content rows.
ソリューション
データベースを変更する前には必ずデータをバックアップするようにします。
The following code snippets should resolve your issue. These queries were run in MySQL, but this sql should work most databases with some small modifications.
Make sure to check the diagnosis section above before running this operation.
create table foo (deletevalues int);
insert into foo select BODYCONTENT.contentid from BODYCONTENT left join CONTENT on BODYCONTENT.contentid=CONTENT.contentid where BODYCONTENT.contentid is not null and CONTENT.contentid is null;
delete from BODYCONTENT where contentid in (select deletevalues from foo);
drop table foo;