Issue Summary

Confluence upgrade to 7.12.1 fails if the DB server already has another database with Confluence 7.12.1 installed.

Steps to Reproduce

1. Create two databases test1 and test2 on the same MySQL server.
2. Install Confluence 7.12.1 using test1 database
3. Install a separate instance of Confluence 7.10.0 using test2 database
4. Upgarde the second instance to Confluence 7.12.1

Expected Results

The upgrade finishes successfully.

Actual Results

Upgrade fails.
Confluence detects that the index denormalised_content_view_permissions_denorm_content_sid_idx exists and tries to delete it. In reality, the index exists in another database, not in the current database, so it fails to drop the index from the current database.

The below exception is thrown and can be found in Confluence logs:

2021-05-20 21:25:31,793 ERROR [Catalina-utility-1] [atlassian.confluence.plugin.PluginFrameworkContextListener] launchUpgrades Upgrade failed, application will not start: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedContentPermissionsUpgradeTask@6802d18 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [DROP INDEX denormalised_content_view_permissions_denorm_content_sid_idx ON DENORMALISED_CONTENT_VIEW_PERMISSIONS;]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Can't DROP 'denormalised_content_view_permissions_denorm_content_sid_idx'; check that column/key exists
 com.atlassian.confluence.upgrade.UpgradeException: Upgrade task com.atlassian.confluence.upgrade.upgradetask.DenormalisedContentPermissionsUpgradeTask@6802d18 failed during the SCHEMA_UPGRADE phase due to: StatementCallback; bad SQL grammar [DROP INDEX denormalised_content_view_permissions_denorm_content_sid_idx ON DENORMALISED_CONTENT_VIEW_PERMISSIONS;]; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Can't DROP 'denormalised_content_view_permissions_denorm_content_sid_idx'; check that column/key exists

Workaround

1. Drop these tables on the current database:

   DROP TABLE DENORMALISED_STATE;
   DROP TABLE DENORMALISED_CONTENT;
   DROP TABLE DENORMALISED_SID;
   DROP TABLE DENORMALISED_SPACE_VIEW_PERMISSIONS;
   DROP TABLE DENORMALISED_SPACE_EDIT_PERMISSIONS;
   

2. On the same MySQL DB server, find all other databases having 'denormalised_content_view_permissions_denorm_content_sid_idx' index. Can be done with this SQL query:

   SELECT DISTINCT TABLE_SCHEMA FROM information_schema.statistics WHERE INDEX_NAME = 'denormalised_content_view_permissions_denorm_content_sid_idx';
   

3. Drop this index on all those databases with this SQL query:

   DROP INDEX denormalised_content_view_permissions_denorm_content_sid_idx ON DENORMALISED_CONTENT_VIEW_PERMISSIONS;
   

   Keep the list of these databases.

4. Restart Confluence and let it finish the upgrade process.
5. Restore the index on all the databases (from item 3) back:

   CREATE INDEX denormalised_content_view_permissions_denorm_content_sid_idx ON DENORMALISED_CONTENT_VIEW_PERMISSIONS (CONTENT_ID, SID_ID);