Confluence index error on upgrade from 4.2.12 to 4.3

[Glenn Garraway]

Hi everyone.

I've had some issues with the upgrade of Confluence from 4.2.12 to 4.3. After a couple of attempts, I found the following error in the logs...

2012-09-13 08:50:57,996 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: create index attch_data_idx on ATTACHMENTDATA (ATTACHMENTID)

2012-09-13 08:50:58,003 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-01408: such column list already indexed

2012-09-13 08:50:58,007 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update

java.sql.SQLException: ORA-01408: such column list already indexed

It turns out that the ATTACHMENTDATA table has a unique contraint (on ATTACHMENTID) that has an identical index to the one being created by the upgrade process. When I removed the unique constraint, the original index was removed and the upgrade (and creation of the new index) was successful.

I attempted to re-add the unique constraint after the upgrade, but I get an error telling me that the index already exists (which makes sense given the same index was created during the upgrade above).

My question is, is this a bug, and is there any danger in not having the unique constraint on the ATTACHMENTDATA table in place?

Cheers.

- Glenn

[Thor · Accepted solution]

I had a similar problem upgrading from 4.2.1 to 4.3.1 (sorry part of the error is in Danish due to our Oracle version):

2012-09-19 11:17:38,642 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: create index attch_data_idx on ATTACHMENTDATA (ATTACHMENTID)
2012-09-19 11:17:38,642 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-01408: kolonnelisten er allerede indekseret

2012-09-19 11:17:38,642 ERROR [main] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLException: ORA-01408: kolonnelisten er allerede indekseret

Investigating further it turns out that Oracle has already created this index because the column is a foreign key. It seems that Hibernate fails to detect that. I am wondering whether it is because the Hibernate dialect it Oracle9 and the database is an 11.

Anyway adding the following to confluence.cfg.xml enable the upgrade to run:

<property name="hibernate.hbm2ddl.skip_creating_missing_indexes">true</property>

[Glenn Garraway]

Thanks for the info, that's consistent with the work around suggested by Daniel above.

We have a production upgrade due sometime in the next 6 months so i'll see how the above suggestions go.

Thanks again.

[Daniel Borcherding]

It looks like this issue may have been addressed in a previous answers post.

Please give the following a peruse and let us know if the workaround described solved your issues

https://answers.atlassian.com/questions/79402/issues-upgrading-confluence-from-4-1-to-4-2

As far as the danger of not having a unique constaint on the ATAACHMENTDATA table I am not entirely sure. Hopefully this workaround will make it unnecessary to remove this constraint.

[Glenn Garraway]

Thanks for the reply Daniel.

I'll roll back the upgrade and give that a test.

Cheers.

- Glenn

[Glenn Garraway]

Looks like I can't roll back my development environment to a pre-upgrade version of Confluence to properly test this workaround. Can someone report on whether this workaround was successful?

[Sinead O'Neill]

I am seeing the same errors when I attempt to upgrade to Confluence 4.3 from 4.2.11.