How to change your Confluence links in Jira after a migration


Posted by
Rémi De Chalendar

April 3, 2018

Doing a server migration when your Confluence and Jira instances are linked is not a procedure to take lightly, whether you are merging instances, separating instances or changing the domain name.

When your two applications contain links, they need to be updated to attach to the new server name and potentially the new Confluence pages’ IDs. This situation does not apply when Confluence links are directed to Jira because those types of links are dependent on application links. But links from Jira to Confluence retain the previous server name, along with the previous page IDs so they must be notified of the migration en mass via the database.

In the following article, we will explain two processes that allows you to make sure your Jira and Confluence links remain intact between the two applications.The game plan you’ll use depends on the type of migration performed.

Just follow the steps below:

  • Case 1: Only the server name changed. Page IDs are intact. Here, you’ll perform a migration by copying the home folder and dump/restore the database when you preform your migration via a Confluence export and import. This only requires access to the Jira database on the target server.
  • Case 2: Both the server name and page IDs changed. Here you’ll need to perform a migration by exporting and importing at the space level. In this situation, we’ll need access to the Confluence database on the source and target server along with Jira’s target server.

Case 1: Update the server name only

In this case, only the server name needs to be updated in order to reestablish the links from Jira to Confluence.

A SQL query must be applied to the Jira database on the target server. Here’s how to do this:

Before applying the query, you need to stop the Jira application. It is imperative you backup your database.

To update the links for the Confluence pages in Jira, you need to modify the remotelink table. Where it mentions the old server name in the Jira database, replace it with the new name.

So this:

SELECT ISSUEID, APPLICATIONTYPE, APPLICATIONNAME, URL FROM remotelink ;
Get the list of existing links from Jira's database

Should be this:

Apply the following query to the Jira database and replace NOM_ANCIEN_SERVEUR by the name of the source server and NOM_NOUVEAU_SERVEUR by the name of the target server:

UPDATE remotelink SET URL=REPLACE(URL,'NOM_ANCIEN_SERVEUR','NOM_NOUVEAU_SERVEUR') WHERE URL like 'https://NOM_ANCIEN_SERVEUR/%' ;

And there you go – mission accomplished.

Case 2: Update both the server name and page IDs

n the event that both the server name and page IDs need to be updated, several steps must be taken:

  1. Retrieve the IDs of the spaces impacted by the migration (optional).
  2. Create a mapping table between old and new page IDs.
  3. Update the links in Jira’s database

The queries for step 1 and 2 are carried out on the Confluence database and do not require a prior backup.

Step 3 are carried out on Jira’s database and thus the application must be stopped first. It is essential to backup the database before doing step 3.

1. Retrieve the IDs from the spaces (this is optional if not all your spaces are impacted)

On the source and target Confluence databases, run the following query to obtain the old and new IDs of the spaces involved in the migration:

SELECT SPACEID, SPACENAME, SPACEKEY FROM SPACES;
Retrieve the space IDs

2. Create the mapping table

Now you must create the map to link the IDs of the pages from the old server to the new server.

While Confluence does not change the page ID when editing a page, it does register the different versions saved. That means there are several IDs for one page.

The table you’ll create will store the name of the pages, along with the IDs which contain additional information beyond just the page.

In order to recover what is useful for the mapping table, you need to:

  • Only get the pages involved (CONTENTTYPE=’PAGE’).
  • The last version of each page (PREVVER is NULL).

The following query retrieves the list of page IDs for the SPACEID_1 and SPACEID_2 spaces:

SELECT CONTENTID, TITLE FROM CONTENT WHERE CONTENTTYPE='PAGE' AND PREVVER is NULL AND SPACEID IN ('SPACEID_1','SPACEID_2');

Note: Replace SPACEID_1 and SPACEID_2 by the list of spaces retrieved in the previous step. Make sure you use the previous IDs on the source server, and the new IDs on the target server.

If all the Confluence spaces are impacted by the migration, then just use the following query:

SELECT CONTENTID, TITLE FROM CONTENT WHERE CONTENTTYPE='PAGE' AND PREVVER is NULL;

To recover the values of the query more easily, you can export the results directly into a CSV file:

mysql BASE_Confluence -u USER -p -e "REQUETE_SQL" | tr '\t' ';' > /PATH/ID_PAGE.csv
MySQL
COPY (REQUETE_SQL) TO '/PATH/ID_PAGE.csv' DELIMITER ';' CSV HEADER;
PostgreSQL

It’s necessary to map the former IDs to the new IDs by using the titles of the pages (which do not change).

You can do the mapping in an Excel file by checking that the names match (and therefore the IDs are aligned) with the formula used in the table below:

ID Source
Source page name
Target page name
ID target
Comparaison (Returns TRUE if the names are the same)
65633 Page name Nom page 1605637 =EXACT(A3;A4)

3. Update the links in the database

We now need to replace the links in the Jira table by using the mapping which was previously created.

The Jira remotelink table contains two values to update:

  • The URL, as the server name and linked page ID needs to be changed
  • The GLOBALID, which only the page ID needs to be modified.

We need to create the query to make these updates. To improve the readability, they will be separated into two parts – one for the URL and another for the GLOBALID.

Both queries will have the same structure:

update remotelink set URL=REPLACE(URL,'OLD_SERVEUR/pages/viewpage.action?pageId=OLD_ID','NEW_SERVEUR/pages/viewpage.action?pageId=NEW_ID') WHERE URL='https://OLD_SERVEUR/pages/viewpage.action?pageId=OLD_ID';
update remotelink set GLOBALID=REPLACE(GLOBALID,'OLD_ID','NEW_ID') WHERE GLOBALID like '%&pageId=OLD_ID';
Retrieve the page IDs (Confluence)

Note: While the “where” is not essential, it allows you to see how many lines apply to each request and therefore is a safer practice.

 
Before running the queries, you’ll need to backup your data and stop the Jira application.
In an Excel file, you can build all your queries in a “CONCAT” formula.
Then you are done.

Need help with your migration?

We’ve touched on one angle to consider when conducting a migration but the process is far from simple. If you want to ensure you’ll get it right, our experienced Atlassian certified consultants at Valiantys are here to help. Just click below and we’ll get in touch to discuss your project.