Jira upgrade throws: Error was: java.sql.SQLException: ORA-01408: such column list already indexed

Still need help?

The Atlassian Community is here for you.

Ask the community

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 Jira is upgraded to 8.20.8, 8.22.0, or later, the following error message can appear on instances that use Oracle Database:

2022-04-25 23:35:46,615+1000 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "Membership"
2022-04-25 23:35:46,615+1000 JIRA-Bootstrap ERROR      [o.o.c.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
    CREATE UNIQUE INDEX uk_mem_dir_parent_child ON cwd_membership (lower_parent_name, lower_child_name, membership_type, directory_id)
    Error was: java.sql.SQLException: ORA-01408: such column list already indexed

This index is then automatically created by an upgrade task (UpgradeTask_Build820008UpgradeTask_Build822000), so most likely no action is required. However, you can also verify that the index was created correctly to make sure there will be no issues further on.

Cause

In Jira 8.20.8 and 8.22.0, we replaced the non-unique idx_mem_dir_parent_child  index with the unique uk_mem_dir_parent_child  index. We made this change to mitigate the following issue: JRASERVER-70690 - Getting issue details... STATUS .

For technical reasons, we first add the new index and only then remove the old one. However, Oracle Database doesn’t allow creating two indexes on the same set of columns, thus causing the java.sql.SQLException error. 

Result

As a result, the new index is not created by the standard schema verification procedure. Luckily, there is an upgrade task that removes the old index and verifies the new one has been created. When the upgrade task detects that uk_mem_dir_parent_child is missing, it automatically recreates it.

Manual verification

You might want to manually verify the uk_mem_dir_parent_child  index has been created correctly. You can do that in one of the following ways.

Option 1. Check upgrade task log

The upgrade log should contain an entry saying "Added index uk_mem_dir_parent_child".

Option 2. Query Oracle Database

Run the following query:

Query DBA_INDEXES
SELECT INDEX_NAME, UNIQUENESS
FROM SYS.DBA_INDEXES
WHERE TABLE_NAME = 'CWD_MEMBERSHIP'
AND INDEX_NAME = 'UK_MEM_DIR_PARENT_CHILD';

If the query returns the following response, the indexes on this table are set up correctly:

+-----------------------+----------+
|INDEX_NAME             |UNIQUENESS|
+-----------------------+----------+
|PK_CWD_MEMBERSHIP      |UNIQUE    |
|UK_MEM_DIR_PARENT_CHILD|UNIQUE    |
|IDX_MEM_DIR_CHILD      |NONUNIQUE |
|IDX_MEM_DIR_PARENT     |NONUNIQUE |
|IDX_MEM_TYPE_CHILD_NAME|NONUNIQUE |
+-----------------------+----------+

What should you do if the uk_mem_dir_parent_child index is missing?

We expect the uk_mem_dir_parent_child index to be there. If you can't verify the index presence, contact Atlassian Support for further investigation.


DescriptionVerifying uk_mem_dir_parent_child index after upgrading to Jira 8.20.8/8.22.0 with Oracle DB
ProductJira

Last modified on May 18, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.