-
Bug
-
Resolution: Low Engagement
-
Medium
-
None
-
5.7
-
19
-
Severity 2 - Major
-
4
-
Yes, this is a duplicate of CONF-32139, but it is because that issue was marked as Resolved because there was a desire not to delete pre-existing indexes.
I think the proper resolution here is to do some checking in the upgrade task to determine if the index already exists and if so, use that index versus creating a new index. I know that SailPoint did this when we had many hundreds of clients running Oracle who added indexes for performance reasons through the Statistics and Query Analyser tools that improved performance in our application. This made the upgrade tasks run smoothly to finish without upsetting the clients who had already added indexes by halting their upgrade tasks.
I would like to have this issue revisited as our largest clients are running Oracle backends and will see this issue in their environments. If we checked for the existence of an index on the table and column that we are about to index and then did not do so if it already exists, then the upgrade experience would be smoother overall.
- mentioned in
-
![[Atlassian Documentation] Page](/images/icons/generic_link_16.png "[Atlassian Documentation] Page")
Page
No Confluence page found with the given URL.
-
Page
No Confluence page found with the given URL.
-
Page
No Confluence page found with the given URL.
-
Page
No Confluence page found with the given URL.
-
Page
No Confluence page found with the given URL.
-
Page Loading...
-
[CONFSERVER-36652] Oracle Upgrade Fails due to pre-existing indexes
Dana Jansen
added a comment - @Todd Could you please comment with the duplicate indexes that you saw in oracle?
Dana Jansen
added a comment - @Todd Could you please comment with the duplicate indexes that you saw in oracle? I see this issue on our confluence upgrade from 5.0.3 to 5.8.10 with oracle backend.
This problem is not limited to Oracle. It seems the upgrade tasks in Confluence has some gaps. Our test upgrade form 5.5.6 to 5.7.3 produced a bunch of duplicate indexes in MySQL 5.6.
2015-06-16 20:03:29,639 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_SPACEPERMISSIONS_CREATOR] are present on table 'SPACEPERMISSIONS' for columns [creator]. New index 'sp_creator_idx' will be a duplicate. 2015-06-16 20:03:29,639 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_SPACEPERMISSIONS_LASTMODIFI] are present on table 'SPACEPERMISSIONS' for columns [lastmodifier]. New index 'sp_lastmodifier_idx' will be a duplicate. 2015-06-16 20:03:29,639 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK76F874F73AEE0F] are present on table 'cwd_user_credential_record' for columns [user_id]. New index 'idx_user_cred_record_user_id' will be a duplicate. 2015-06-16 20:03:29,640 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_SPACES_CREATOR] are present on table 'SPACES' for columns [creator]. New index 's_creator_idx' will be a duplicate. 2015-06-16 20:03:29,640 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_SPACES_LASTMODIFIER] are present on table 'SPACES' for columns [lastmodifier]. New index 's_lastmodifier_idx' will be a duplicate. 2015-06-16 20:03:29,640 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [fk_child_user] are present on table 'cwd_membership' for columns [child_user_id]. New index 'idx_mem_dir_child_user' will be a duplicate. 2015-06-16 20:03:29,640 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_CONTENT_LABEL_OWNER] are present on table 'CONTENT_LABEL' for columns [owner]. New index 'cl_owner_idx' will be a duplicate. 2015-06-16 20:03:29,640 WARN [localhost-startStop-1] [hibernate.tool.hbm2ddl.PreExistingIndexChecker] warnIfExistingMatchingIndexesPresent Pre-existing indexes [FK_PAGETEMPLATES_CREATOR] are present on table 'PAGETEMPLATES' for columns [creator]. New index 'pt_creator_idx' will be a duplicate.
Why did Confluence change the name of the indexes? And if it did, why no clean-up task to remove the duplicates?
It's also probably not a good idea to leave duplicate indexes around performance-wise.
Clarifying my earlier typographical error, this is a Duplicate of CONF-32139, the issue does still occur when running an upgrade task for our largest clients running Oracle 11gR2 as their backend repository and that resolution was CLOSED as Atlassian was not going to DELETE pre-existing indexes.
However, there is an alternative and more elegant solution, which would be to skip the creation of pre-existing indexes altogether and allow the indexes already in place to remain and the upgrade to continue apace without interruption. We did this at SailPoint to avoid the bad Enterprise experience of a "Failed Upgrade" due to pre-existing indexes. This leaves the client worried that the Upgrade is somehow unstable, and the end-result is that we are placing the deletion onus on the client for an index that we intend to use that already exists.
If all we need is to have the indexes in place, then they already exist. I am not sure why it is necessary to halt the upgrade with an ORA-01408 error telling them that the index already exists and the upgrade has failed. This just forces them to go back and remove the indexes that were already added so that we can then add the indexes. This seems like we have avoided the deletion only to fail the upgrade anyway. Why not just skip the creation of an index if it already exists in the database?
This should add value to the experience of the Enterprise and Premier level clients that spent their money on Oracle backends to use the database tool as built and improve their experience with upgrading Confluence. As it stands now, the client receives the jarring 'Upgrade Failed: ORA-01408 ERROR' messages and worries that the upgrade will be less than successful or may be somewhat 'flaky' given this occurrence.
What I would like to determine is whether or not we can simply use the indexes in place and successfully skip creating new indexes when they are already in place. It has been done before, and without a reason to skip them, it is hard to think that the only solution is deletion and recreation of the indexes.
Hi,
At Atlassian, our goal is to ensure we’re providing the best experience for our customers. With our new Data Center strategy, Atlassian's focus is on security, compliance, and performance and is a key driver in prioritizing bugs. Closing the bugs that do not fall into those categories will allow us to focus on the ones in the most current versions of our products.
This bug is being closed due to a lack of engagement in the last four years, including no new watchers, votes, or comments; this inactivity suggests a low impact.
Please note the comments on this thread are not being monitored.
You can read more about our bug fix policy here and how we prioritize bugs.
To learn more about our recent investments in Confluence Data Center, please check our public roadmap and dashboards containing recently resolved issues, current work, and future plans.
Kind regards,
Confluence Data Center