Upgrade fails due to a constraint violation on BODYCONTENT
Problem
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
Diagnosis
Run the following query against your database:
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.
Cause
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.
Resolution
Always backup your data before performing any modifications to the database.
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;