Problem Definition:-
Bamboo has a repeatable upgrade task (60204) that creates foreign key indices for DBMS that don't do this by default.
The problem with this upgrade task is that finding out which indices already exists and are correct, This is hard to do and can be dodgy on some DBMS-es, so it's easier to simply attempt to create again. The issue occurs mostly for Oracle DB.
Just before the Index creation finished , we see warning message with with error ORA-01408.
Index creation time increases thus increasing the upgrade time
2024-08-22 20:38:50,819 INFO [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Creating indices... 2024-08-22 20:38:53,280 INFO [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Creating index fkix_7a45fb_aotrac1186792941 on AO_7A45FB_AOTRACKING_RESULT (LINKED_TRACKING_ENTRY_ID) 2024-08-22 20:38:53,291 WARN [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Couldn't create index. If an equivalent index has been manually set up, remove it, it's no longer neeed. Error: java.sql.SQLException: ORA-01408: such column list already indexed 2024-08-22 20:38:53,462 INFO [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Creating index fkix_7a45fb_aotrac871242725 on AO_7A45FB_AOTRACKING_USER (LINKED_TRACKING_ENTRY_ID) 2024-08-22 20:38:53,471 WARN [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Couldn't create index. If an equivalent index has been manually set up, remove it, it's no longer neeed. Error: java.sql.SQLException: ORA-01408: such column list already indexed 2024-08-22 20:38:53,599 INFO [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Creating index fkix_a0b856_webhoo1293196287 on AO_A0B856_WEBHOOK_CONFIG (WEBHOOKID) 2024-08-22 20:38:53,607 WARN [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Couldn't create index. If an equivalent index has been manually set up, remove it, it's no longer neeed. Error: java.sql.SQLException: ORA-01408: such column list already indexed 2024-08-22 20:38:53,730 INFO [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Creating index fkix_a0b856_webhoo1143974467 on AO_A0B856_WEBHOOK_EVENT (WEBHOOKID) 2024-08-22 20:38:53,738 WARN [16-UpgradeTaskBackgroundThread:pool-28-thread-1] [XmlMigrator] Couldn't create index. If an equivalent index has been manually set up, remove it, it's no longer neeed. Error: java.sql.SQLException: ORA-01408: such column list already indexed
Suggested Solution
Allow this Task to run on every startup, similar to the schema check process.It actually tries to only create indices that are missing; this might be a problem with active objects specifically creating functionally identical indices but named differently.
Why this is important
The lack of any indices in the Bamboo database can cause a significant degradation in performance. In extreme cases, queries that may take over 10 hours without indices run in a seconds once they are added and refreshed.
Workaround
- The workaround for now, is to drop the index . 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.
Always back up your data before performing any modifications to the database. If possible, try your modifications on a test server.
DROP INDEX <<Index Name>>
- Upon restarting, Bamboo will rebuild the missing index.