Summary

When importing Spaces from another instance where the Space Key already exists, the Space Key must be changed before importing the Space. This process is described in How to copy or rename a space in Confluence - Solution 3.

When merging two instances, one of the imported Spaces may have links on its page pointing to a Page in a Space where its key was changed. This can lead to links needing to be fixed or pointing to pages in the wrong Space.

Environment

A Confluence Data Center instance where you are importing two or more Spaces, and one of the Spaces had its key changed.

Diagnosis

To identify the links facing the issue that needs to be fixed, run the query below:

select c.* FROM content c
    JOIN bodycontent bc ON c.contentid = bc.contentid
    JOIN spaces s ON s.spaceid = c.spaceid
where contenttype = 'PAGE'
and prevver is NULL
and content_status = 'current'
and bc.body ~ '<ac:link><ri:page ri:space-key="(<list of space keys before the change>)"'
and spacekey in (<list of all space keys imported>)

Replace <list of space keys before the change> with the list of the original Space key from the Spaces that had their keys changed, separated by "|".

Replace <list of all space keys imported> with the list of all Space keys imported, with a single quote and separated by a comma. For the Spaces where their keys were changed, use the new key.

For example, consider these Spaces were imported:

In this scenario:

• <list of space keys before the change> will be replaced by "DEPX|PEX"
• <list of all space keys imported> will be replaced by "'DEPX1', 'PEX1', 'SE', 'DEVOPS', 'ATPX'"

The query will be:

select c.* FROM content c
    JOIN bodycontent bc ON c.contentid = bc.contentid
    JOIN spaces s ON s.spaceid = c.spaceid
where contenttype = 'PAGE'
and prevver is NULL
and content_status = 'current'
and bc.body ~ '<ac:link><ri:page ri:space-key="(DEPX|PEX)"'
and spacekey in ('DEPX1', 'PEX1', 'SE', 'DEVOPS', 'ATPX')

Cause

It happens because the change of the Space key, as described in How to copy or rename a space in Confluence - Solution 3, is done in the Space itself. However, the links in other imported spaces pointing to that space need to pass through the same process to change the key in the links.

Solution

You must change the links in the page storage format to fix them.

Follow these steps:

1. Stop Confluence
2. Backup Confluence Database

3. Run the following query to create a list of pages with links that need to be fixed:

   CREATE TABLE UPDATE_SPACE_KEY AS 
   (
       select c.contentid FROM content c
           JOIN bodycontent bc ON c.contentid = bc.contentid
           JOIN spaces s ON s.spaceid = c.spaceid
       where contenttype = 'PAGE'
       and prevver is NULL
       and content_status = 'current'
       and bc.body ~ '<ac:link><ri:page ri:space-key="(<list of space keys before the change>)"'
       and spacekey in (<list of all space keys imported>)
   );

   Replace <list of space keys before the change> with the list of the original Space key from the Spaces that had their keys changed, separated by "|".
   Replace <list of all space keys imported> with the list of all Space keys imported, with a single quote and separated by a comma. For the Spaces where their keys were changed, use the new key.
   Using the scenario from the Diagnosis section, the query will be:

   CREATE TABLE UPDATE_SPACE_KEY AS 
   (
       select c.contentid FROM content c
           JOIN bodycontent bc ON c.contentid = bc.contentid
           JOIN spaces s ON s.spaceid = c.spaceid
       where contenttype = 'PAGE'
       and prevver is NULL
       and content_status = 'current'
       and bc.body ~ '<ac:link><ri:page ri:space-key="(DEPX|PEX)"'
       and spacekey in ('DEPX1', 'PEX1', 'SE', 'DEVOPS', 'ATPX')
   );

4. For each Space Key that needs to be fixed, run an Update like the following, replacing <Space Key before the change> with the original Space Key and <Space Key after the change> with the new Space Key:

   update bodycontent
       SET BODY = REGEXP_REPLACE(BODY,'<ac:link><ri:page ri:space-key="<Space Key before the change>"', '<ac:link><ri:page ri:space-key="<Space Key after the change>"','gs') 
   where contentid in (select contentid from UPDATE_SPACE_KEY)
   and body like '%<ac:link><ri:page ri:space-key="<Space Key before the change>"%';

   Using the example above, it will be necessary to run this query twice, as two spaces had their Space Key changed:

   update bodycontent
       SET BODY = REGEXP_REPLACE(BODY,'<ac:link><ri:page ri:space-key="DEPX"', '<ac:link><ri:page ri:space-key="DEPX1"','gs') 
   where contentid in (select contentid from UPDATE_SPACE_KEY)
   and body like '%<ac:link><ri:page ri:space-key="DEPX"%';
   
   update bodycontent
       SET BODY = REGEXP_REPLACE(BODY,'<ac:link><ri:page ri:space-key="PEX"', '<ac:link><ri:page ri:space-key="PEX1"','gs') 
   where contentid in (select contentid from UPDATE_SPACE_KEY)
   and body like '%<ac:link><ri:page ri:space-key="PEX"%';

5. Drop the table used to create a list of pages of links that need to be fixed:

   DROP TABLE UPDATE_SPACE_KEY;

6. Start Confluence and validate the links are fixed.