Jira upgrade throws: Error was: java.sql.SQLException: ORA-01408: such column list already indexed
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. 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:
1
2
3
4
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 indexedThis index is then automatically created by an upgrade task (UpgradeTask_Build820008 / UpgradeTask_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 - User login fails because inconsistent data / uk_mem_parent_child_type .
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
1
2
3
4
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:
1
2
3
4
5
6
7
8
9
+-----------------------+----------+
|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.
Was this helpful?