Duplicate indexes in MySQL Database during Confluence Upgrade
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
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 creator. Do this for each and every WARN message where a new index would create a duplicate.
Stop Confluence.
Drop the index as:
DROP INDEX FK_SPACEPERMISSIONS_CREATOR on SPACEPERMISSIONS;- Restart Confluence. Confluence 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.