ORA-01408: such column list already indexed

Hi,

I use Jira 6.1 with Oracle 11g. When I start Jira, I get an "ORA-01408: such column list already indexed" error, for various columns:

2013-10-16 10:48:58,120 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "GroupAttribute"
2013-10-16 10:48:58,120 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX idx_group_attr_dir_name_lval ON cwd_group_attributes (directory_id, attribute_name, lower_attribute_value)
Error was: java.sql.SQLException: ORA-01408: such column list already indexed

2013-10-30 12:02:48,136 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "OptionConfiguration"
2013-10-30 12:02:48,136 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX fieldid_fieldconf ON optionconfiguration (FIELDID, FIELDCONFIG)
Error was: java.sql.SQLException: ORA-01408: such column list already indexed

2013-10-30 12:03:32,559 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "KIssueState"
2013-10-30 12:03:32,559 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX ISSUE_STATE_IDX ON kissuestate (ID)
Error was: java.sql.SQLException: ORA-01408: such column list already indexed

2013-10-30 12:03:32,575 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "KPluginsCfg"
2013-10-30 12:03:32,575 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE UNIQUE INDEX PLUGIN_CFG_IDX ON kpluginscfg (PLUGIN_ID, CFG_NAME)
Error was: java.sql.SQLException: ORA-01408: such column list already indexed

2013-10-30 12:03:32,575 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] Could not create missing indices for entity "KStateValues"
2013-10-30 12:03:32,575 localhost-startStop-1 ERROR      [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
CREATE INDEX STATE_VALUES_IDX ON kstatevalues (ID)
Error was: java.sql.SQLException: ORA-01408: such column list already indexed

I have resolved the first of those errors thanks to that KB article. Do you think I should try to resolve the 4 other errors by dropping other indexes, for example fieldid_fieldconfig?

2 answers

1 accepted

Yes. But always take backup before going to do modifications on database.

Follow steps in the above artical.

Ok, but how can I be sure which index I have to Drop?

I have already resolved the first error by dropping IDX_GOUP_ATTR_DIR_NAME_LVAL.

For the second error, it could also be a typing error between FIELDID_FIELCONFIG and FIELDID_FIELDCONF. In that case I should drop FIELDID_FIELCONFIG.

But for the other indexes (ISSUE_STATE_IDX, INDEX PLUGIN_CFG_IDX and STATE_VALUES_IDX), I can't find indexes in my database whose name are almost similar...

It worked with the second error. I dropped the index FIELDID_FIELDCONFIG, and on the next Jira started, it was replaced by FIELDID_FIELDCONF, and the error message disappeared.

I tried dropping ISSUE_STATE_ISSUE_KEY_IDX and STATE_VALUES_STATE_IDX to correct the 3rd and the 5th error, but apparently they aren't the correct indexes, as the same indexes got recreated on next startup and the errors were still there.

For the 3 remaining errors, I renamed the indexes and the errors disappeared :

ALTER INDEX PK_KISSUESTATE
RENAME TO ISSUE_STATE_IDX

ALTER INDEX PK_KPLUGINSCFG
RENAME TO PLUGIN_CFG_IDX

ALTER INDEX PK_KSTATEVALUES
RENAME TO STATE_VALUES_IDX

Suggest an answer

Log in or Join to answer
Community showcase
Sarah Schuster
Posted Jan 29, 2018 in Jira

What are common themes you've seen across successful & failed Jira Software implementations?

Hey everyone! My name is Sarah Schuster, and I'm a Customer Success Manager in Atlassian specializing in Jira Software Cloud. Over the next few weeks I will be posting discussion topics (8 total) to ...

3,264 views 14 20
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot