Problem

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. 

The following appears in the 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.

Diagnosis

Environment

• Oracle database

Cause

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  CONF-36652 - Getting issue details... STATUS .

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 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 indexed

where 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.

1. Stop Confluence.

2. 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_position

3. Drop each index that uses the column using the following query:

    

   DROP INDEX <<Index Name>>

4. Start Confluence. Upon restarting, Confluence will rebuild the missing index.