要約

Attachment count is different in the database after migration from 5.x to 6.x , this can create issues if the customer plans to migrate to Atlassian cloud as all the attachments will not be migrated

環境

Upgrade from 5.x.x to 6.x.x 

診断

• Post the Upgrade below query can be used to compare the difference in the count of attachments on the old instance and new instance

  SELECT count(*),S.SPACEKEY from CONTENT C , SPACES S
  where C.CONTENTTYPE='ATTACHMENT' and C.SPACEID = S.SPACEID and SPACEKEY IN ('XXX', 'YYY')
  GROUP BY S.SPACEKEY;

• It might also be observed that the above query shows different counts at different times

原因

• The older versions (before 6.0) of Confluence stores the attachment information in the attachment table.
• During the upgrade, attachment records are migrated from the attachments table to the Content table, but with NULL values for SpaceID for all attachments.
• When a page is visited in the Confluence UI, the respective attachment records in the Content table get updated with the correct SpaceID.
• This behavior explains why different counts for attachments are observed at different times.
• Once when a user visits a page, the attachment records for that page in the Content table are updated with the proper SpaceID.

ソリューション

• • After the upgrade, SpaceID needs to be manually updated using a SQL script 
  • Create a database backup 

  • • Use the below SQL query to check the count of attachments with NULL SpaceID:

      select count(*),contenttype from content where spaceid is NULL and Contenttype='ATTACHMENT' group by contenttype;

    • Follow the below steps to create the update statements for those records which have null SpaceID.

      -- find all attachments that have no spaceid
      drop table if exists tempdatabg;
      create table tempdatabg as 
      (select spaceid, contentid from content where contentid in (select distinct pageid from content where CONTENTTYPE='ATTACHMENT' and SPACEID is null ) and spaceid is not null);
      
      -- generate sql statements to update the space id form the attachments found with the space id of the page where they belong to
      -- the output should be saved as sql file, so it can be used to update the attachments
      select concat('update content set spaceid= ''',spaceid,''' where Pageid= ''',contentid ,''' and contenttype=''ATTACHMENT'' and spaceID is NULL;') from tempdatabg;

    • Use the below SQL query to verify if all attachments are updated with SpaceID 

      select count(*),contenttype from content where spaceid is NULL and Contenttype='ATTACHMENT' group by contenttype;