Duplicate indexes in MySQL Database during Confluence Upgrade

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Problem

When upgrading to Confluence 5.x from previous versions, there is a warning for duplicate indexes found in Confluence logs which cause the upgrade to fail.

The following appears in the atlassian-confluence-log

2016-04-28 22:44:06,056 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_LINKS_CREATOR] are present on table 'LINKS' for columns [creator]. New index 'links_creator_idx' will be a duplicate.
2016-04-28 22:44:06,056 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_TRACKBACKLINKS_CREATOR] are present on table 'TRACKBACKLINKS' for columns [creator]. New index 'tbl_creator_idx' will be a duplicate.
2016-04-28 22:44:06,057 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_TRACKBACKLINKS_LASTMODIFIER] are present on table 'TRACKBACKLINKS' for columns [lastmodifier]. New index 'tbl_lastmodifier_idx' will be a duplicate.
2016-04-28 22:44:06,057 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_CONTENT_LASTMODIFIER] are present on table 'CONTENT' for columns [lastmodifier]. New index 'c_lastmodifier_idx' will be a duplicate.
2016-04-28 22:44:06,058 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_NOTIFICATIONS_USERNAME] are present on table 'NOTIFICATIONS' for columns [username]. New index 'n_username_idx' will be a duplicate.
2016-04-28 22:44:06,058 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_NOTIFICATIONS_CREATOR] are present on table 'NOTIFICATIONS' for columns [creator]. New index 'n_creator_idx' will be a duplicate.
2016-04-28 22:44:06,058 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_NOTIFICATIONS_LASTMODIFIER] are present on table 'NOTIFICATIONS' for columns [lastmodifier]. New index 'n_lastmodifier_idx' will be a duplicate.
2016-04-28 22:44:06,058 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_CONTENT_PERM_LASTMODIFIER] are present on table 'CONTENT_PERM' for columns [lastmodifier]. New index 'cp_lastmodifier_idx' will be a duplicate.
2016-04-28 22:44:06,058 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_CONTENT_PERM_CREATOR] are present on table 'CONTENT_PERM' for columns [creator]. New index 'cp_creator_idx' will be a duplicate.

Diagnosis

Environment

  • MySQL Database

Cause

This is caused by a duplicate indexes constraint within MySQL, where it does not allow the creation of duplicate indexes in the database. Pre-existing indexes must be addressed before the upgrade will successfully complete.

Resolution

The resolution in this instance is to drop the indexes on the Columns that pre-exist where a new index will be created. As these indexes are now a part of Confluence, they need to be dropped as the current script does not skip creation if the index already exists. So, for each instance where a message like:

 

2016-04-28 22:44:05,094 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_SPACEPERMISSIONS_CREATOR] are present on table 'SPACEPERMISSIONS' for columns [creator]. New index 'sp_creator_idx' will be a duplicate.

The workaround is to drop the pre-existing indexes on the columns in the tables listed.

For example, the first message indicates that the index: FK_SPACEPERMISSIONS_CREATOR already exists on Table SPACEPERMISSIONS on Column creator. The correct workaround, for now, is to drop the index FK_SPACEPERMISSIONS_CREATOR in table SPACEPERMISSIONS on column creatorDo this for each and every WARN message where a new index would create a duplicate.

  1. Stop Confluence.

  2. Drop the index as:

    DROP INDEX FK_SPACEPERMISSIONS_CREATOR on SPACEPERMISSIONS;
  3. Restart ConfluenceConfluence will rebuild the missing index upon restarting.

Always back up your data before performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Last modified on Oct 7, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.