Duplicate indexes in MySQL Database during Confluence Upgrade
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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
1
2
3
4
5
6
7
8
9
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.
Solution
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:
1
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 creator. Do this for each and every WARN message where a new index would create a duplicate.
Stop Confluence.
Drop the index as:
1DROP INDEX FK_SPACEPERMISSIONS_CREATOR on SPACEPERMISSIONS;Restart Confluence. Confluence will rebuild the missing index upon restarting.
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Was this helpful?