Upgrade from 3.3.3 to 3.5.17 fails with Oracle-DB

[Andre Lehmann]

Hi,

to stay in support we want to upgrade to 3.5 (and this version we also have to upgrade if we upgrade to 4.x later).

I made all custom changes (server.xml, setenv.sh, atlassian-user.xml) and started the new Confluence-Version.

The upgrade stops with an db-error.

I will attach the complete Log-file

Kind regards

André

[Andre Lehmann · Accepted solution]

Answer from Atlassian Support:

This is due to a dependency in the database that the upgrade is trying to apply. It is a foreign key constraint on the BODYCONTENT table - the CONTENTID field must reference an entry in the CONTENT table. You'll see this error when there are BODYCONTENT entries with CONTENTIDs that do not exist in the CONTENT table. These two tables are used to hold page information.

What you'll need to do is firstly do some backups of your database and file system, just in case anything goes wrong, then run some SQL queries against the oracle database:

select * from BODYCONTENT where CONTENTID not in (select CONTENTID from CONTENT);

This should give you some results. It is safe to delete them by running this command:

delete from BODYCONTENT where BODYCONTENTID = <the bodycontentid value from the above query>;

Don't worry that you'll be losing pages by the way - this is orphaned corrupt data that we're removing. Once that's done the constraint should be successfully added to the database during the upgrade, which will mean these kinds of orphans can't happen again.

[Gerin Abraham]

I had this same issue when doing a Confluence 3.5 to 5.0.3 upgrade. Ran the above SQLs to resolve this and proceed with the upgrade. Thank You !

[Andre Lehmann]

2012-09-27 08:30:58,331 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: alter table BODYCONTENT add constraint FKA898D4778DD41734 foreign key (CONTENTID) references CONTENT
2012-09-27 08:30:58,332 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-02298: cannot validate (CONFLUENCE.FKA898D4778DD41734) - parent keys not found

2012-09-27 08:30:58,333 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLIntegrityConstraintViolationException: ORA-02298: cannot validate (CONFLUENCE.FKA898D4778DD41734) - parent keys not found

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-02298: cannot validate (CONFLUENCE.FKA898D4778DD41734) - parent keys not found