Problem

When upgrading Confluence from version 5.x to version 6.x, the upgrade hangs and no messages are printed to atlassian-confluence.log.

If you check on the database, the process will be stuck on the following query:

UPDATE CONTENTPROPERTIES SET STRINGVAL = :1 where PROPERTYNAME = :2 AND CONTENTID in (SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = :3 AND LOWERTITLE LIKE :4 )

If you let the application in that state for hours, it will continue stuck running the query above and will not finish the process.

Diagnosis

Environment

• Confluence Data Center
• Upgrading from version 5.x to 6.x
• Oracle 12c Database
• JDBC driver 12.2.0.1

Cause

It is still unclear what is the cause of this issue as the support staff has been unable to reproduce it so far. What we know is that the mentioned query was added as a fix for the following bug:

• CONFSERVER-26926 - Getting issue details... STATUS

This would indicate that depending on the number of csv attachments, the tables involved would have a large size that would require the creation of indexes to speed up the process.

Workaround

1. Copy the content of the current CONTENTPROPERTIES to another table:

   create table CONTENTPROPERTIES_BACKUP as select * from CONTENTPROPERTIES;

2. Delete the entries in the CONTENTPROPERTIES table.

   delete from CONTENTPROPERTIES;

3. Perform the upgrade.
4. Shutdown the Confluence after the upgrade is complete (after you are able to reach the dashboard).

5. Run the query below to apply the update query to fix the entries from CONTENTPROPERTIES_BACKUP. This is essentially the upgrade task that was constantly hanging and caused the upgrade to get stuck:

   UPDATE CONTENTPROPERTIES_BACKUP
   SET STRINGVAL = 'text/csv'
   WHERE PROPERTYNAME = 'MEDIA_TYPE'
   AND CONTENTID IN ( SELECT CONTENTID FROM CONTENT
   WHERE CONTENTTYPE = 'ATTACHMENT'
   AND TITLE LIKE '%.csv');

6. Insert the entries back to the CONTENTPROPERTIES table:

   insert into CONTENTPROPERTIES SELECT * FROM CONTENTPROPERTIES_BACKUP;

7. Drop the backup table:

   drop table CONTENTPROPERTIES_BACKUP;

There are no changes in the structure of the CONTENTPROPERTIES table from 5.x to 6.x, therefore, it should be safe to skip the CONTENTPROPERTIES upgrade task using this method.