Space id is not updated for custom objects when their containers are moved to another space

Issue Summary

Space id is not updated for custom objects when their containers are moved to another space.

It affects the XML backup/restore functionality because the XML backup file contains more than one space: https://jira.atlassian.com/browse/CONFSERVER-55366

Steps to Reproduce

1. Create a page A in space X
2. Attach a custom object B to page A so the page becomes a container for the custom object (pageid for the custom object B should refer to page A). The record can be inserted manually into the DB or added via REST API.
3. Check that both page A and the custom object B have the same space id.
4. Flush all caches or restart Confluence if the custom object record was added manually into the DB.
5. Move page A to another space Y.

Expected Results

"spaceid" field is updated for the custom object B.

Actual Results

"spaceid" is not updated for the custom object B.

Workaround

Important: We strongly recommend you test this on staging first. When ready to go for Production, make sure you take backups as per our Production Backup Strategy

The script below searches for all records affected by this issue:

select container.contentid as container_id, container.title as container_title, container.contenttype as container_type,
        dependant.contentid as dependant_id, dependant.title as dependant_title, dependant.contenttype as dependant_type
    from content container
    join content dependant on container.contentid = dependant.pageid
    where container.spaceid != dependant.spaceid; 

It displays id, title and content type for both the container and its dependant.

The next script fixes all invalid records (it updates containers with the wrong spaceid):

 update content set spaceid = proper_space_id from (
    select container.spaceid as proper_space_id, dependant.contentid as content_id_to_fix
        from content container
        join content dependant on container.contentid = dependant.pageid
        where container.spaceid != dependant.spaceid
)  subquery
    where content.contentid = content_id_to_fix;

Important: after running this script, all caches have to be flushed: https://confluence.atlassian.com/doc/cache-statistics-150472.html

After flushing all caches, it is safe to perform the space export.

For MSSQL: CONFSERVER-36018-workaround-mssql.sql