Upgrade to Confluence 6.0.6+ is taking a long time for large instances

Summary

Upgrading to Confluence 6.2.3 is taking a long time for large instances

Steps to Reproduce

1. Existing Confluence 6.0.5 and below
2. You should have a large set of data (specifically in CONTENT and CONTENTPROPERTIES tables)
3. Upgrade Confluence to 6.0.6 or above

Expected Results

The upgrade should be done within reasonable time

Actual Results

The upgrade takes a long time to finish because of the following upgrade task correctCsvAttachmentMimeTypeUpgradeTask which fixes the following bug -CONFSERVER-26926-.
This upgrade task is executing the following query

UPDATE CONTENTPROPERTIES SET STRINGVAL = 'text/csv' where PROPERTYNAME = 'MEDIA_TYPE' AND CONTENTID in (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND LOWERTITLE LIKE '%.csv');

Note: We have customers who took 15 hours for the upgrade to finish successfully. Others took more than 50 hours and did not finish.

Workaround

Disabling correctCsvAttachmentMimeTypeUpgradeTask

1. Stop Confluence
2. Go to this directory <confluence-install>/confluence/lib and locate the file confluence-6.x.x.jar
3. Take a backup of confluence-6.x.x.jar
4. Open this jar file and locate the file upgradeSubsystemContext.xml
5. Remove any occurrence of the following task correctCsvAttachmentMimeTypeUpgradeTask
6. Save and make sure the jar file is updated
7. Reattempt the upgrade

The upgrade now should skip that upgrade task.

Manually apply the upgrade task

After the upgrade is completed successfully, we have to apply this task. What we are going to do is to split the query into 2 separate parts.

Part 1 - Fetch Content ID

We first need to run the following query to get the list of all content ids that need to be updated

SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND LOWERTITLE LIKE '%.csv';

Save this into a text file and separate the content ids with a comma like this

id1, id2, id3...

Part 2 - Update Content Properties

UPDATE CONTENTPROPERTIES SET STRINGVAL = 'text/csv' where PROPERTYNAME = 'MEDIA_TYPE' AND CONTENTID in (<list-of-ids-fetched-before>);

This should split the heavy work of LIKE and IN and make this update query faster since we have the list of IDs pre-fetched.

Split the updates

In case Part 2 is still taking a long time, some customers have also benefitted from splitting the UPDATE statement into individual updates instead of using the IN clause:

UPDATE CONTENTPROPERTIES SET STRINGVAL = 'text/csv' where PROPERTYNAME = 'MEDIA_TYPE' AND CONTENTID = <first-id-from-part-one>;
UPDATE CONTENTPROPERTIES SET STRINGVAL = 'text/csv' where PROPERTYNAME = 'MEDIA_TYPE' AND CONTENTID = <second-id-from-part-one>;
...
UPDATE CONTENTPROPERTIES SET STRINGVAL = 'text/csv' where PROPERTYNAME = 'MEDIA_TYPE' AND CONTENTID = <last-id-from-part-one>;