Pre-existing Oracle indexes halt upgrade to Confluence with error ORA-01408
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
問題
When upgrading to Confluence 5.x from previous versions, you may find that pre-existing indexes are shown in a WARN state until a final ERROR message abruptly ends the Upgrade process.
atlassian-confluence.log に次のメッセージが表示される。
2015-02-09 15:37:17,556 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent
Pre-existing indexes [IDX_USERNAME] are present on table 'NOTIFICATIONS' for columns [username]. New index 'n_username_idx' will be a duplicate.
2015-02-09 15:37:17,579 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: create index
idx_app_dir_group_mapping on cwd_app_dir_group_mapping (app_dir_mapping_id)
2015-02-09 15:37:17,580 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-01408: such column list already indexed
2015-02-09 15:37:17,580 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLException: ORA-01408: such column list already indexed
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)The WARN messages, in this case, are also significant as these indexes are important in the resolution of this issue.
診断
環境
- Oracle データベース
原因
This is caused by a duplicate indexes constraint within Oracle, wherein Oracle does not allow the creation of duplicate indexes in the database. In some older versions of Confluence, these indices may not have been created automatically. Instead, they were created manually by a database administrator to improve performance. These indices may have different names than those defined in the Confluence upgrade. See CONFSERVER-36652 - Oracle Upgrade Fails due to pre-existing indexes.
ソリューション
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 when using an Oracle database as the current script does not skip creation if the index already exists. So, for each instance where a message like:
015-02-09 15:37:17,556 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent
Pre-existing indexes [IDX_USERNAME] are present on table 'NOTIFICATIONS' for columns [username]. New index 'n_username_idx' will be a duplicate.Exists, down to and including:
2015-02-09 15:37:17,579 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute Unsuccessful: create index
idx_app_dir_group_mapping on cwd_app_dir_group_mapping (app_dir_mapping_id)
2015-02-09 15:37:17,580 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute ORA-01408: such column list already indexed
2015-02-09 15:37:17,580 ERROR [localhost-startStop-1] [hibernate.tool.hbm2ddl.SchemaUpdate] execute could not complete schema update
java.sql.SQLException: ORA-01408: such column list already indexedwhere the upgrade finally fails, 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: IDX_USERNAME already exists on Table NOTIFICATIONS on Column username. The correct workaround, for now, is to drop the index IDX_USERNAME in table NOTIFICATIONS on column username. Do this for each and every WARN message where a new index would create a duplicate, up to and including the final ORA-01408 ERROR message is passed to the logs.
Always back up your data before performing any modifications to the database. If possible, try your modifications on a test server.
- Confluence を停止します。
Run the following SQL query to determine which indices exist on the table in question:
SELECT index_name, column_name, column_position FROM user_ind_columns WHERE table_name='<<Table Name>>' ORDER BY index_name, column_positionDrop each index that uses the column using the following query:
DROP INDEX <<Index Name>>Start Confluence. Upon restarting, Confluence will rebuild the missing index.