ORA-01408: such column list already indexed

[Pierre Cattin]

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?

[rambabu patina · Accepted solution]

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

Follow steps in the above artical.

[Pierre Cattin]

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

[Pierre Cattin]

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.

[rambabu patina]

Have a look on http://www-01.ibm.com/support/docview.wss?uid=swg1IY55758

http://www.techonthenet.com/oracle/errors/ora01408.php

Hope helps you.

[Pierre Cattin]

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

[rambabu patina]

Have a look at https://confluence.atlassian.com/display/JIRAKB/ORA-01408%3A+such+column+list+already+indexed