Confluence cloud migration fails when attachment count is different in the database at different times for a space after upgrade from 5.x.x to 6.x.x
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
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
Environment
Upgrade from 5.x.x to 6.x.x
Diagnosis
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
Cause
- 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.
Solution
- 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;