Duplicate indexes in MySQL Database during Confluence Upgrade
プラットフォームについて: Server および Data Center のみ。この記事は、Server および Data Center プラットフォームのアトラシアン製品にのみ適用されます。
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.
*Fisheye および Crucible は除く
問題
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.
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.診断
環境
- MySQL データベース
原因
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.
ソリューション
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.
Confluence を停止します。
Drop the index as:
DROP INDEX FK_SPACEPERMISSIONS_CREATOR on SPACEPERMISSIONS;- Restart Confluence. Confluence will rebuild the missing index upon restarting.
データベースの変更を行う場合は必ず事前にバックアップを取得してください。可能な場合は、まずステージング サーバーで SQL コマンドの変更、挿入、更新、または削除を行うようにします。