How to update old Confluence page ID and tinyurls with new values inside the Jira Issues

Summary

At times, migration of Confluence pages might be performed with third-party tools, which can cause modification of the page ID or content ID for a page in the target environment. This will cause broken link issues in Jira, where a number of Confluence pages could be linked either with the old page ID, spacekey, or TinyURL.

Environment

9.x

Solution

1. The first step in making the required modification would be to update the new confluence site applink id in the remotelink table. Follow the steps outlined in the KB Rename App-links following "Option 2" in the resolution section.

2. Then we need to update the confluence url in a number of other places in Jira tables where the entry might have been made. We will utilize the KB Update outgoing links.

3. Now, we have updated the new applink ID and new app url, in all the Jira related tables, where the old confluence link might be found. Lets run the below sql in both the old and new confluence sites to gather the page IDs for all the pages in the instance, to store the results in two separate files. For Postgres

   1 2 COPY (SELECT CONTENTID, TITLE FROM CONTENT WHERE CONTENTTYPE='PAGE' AND PREVVER is NULL) TO '/Users/Admin/Downloads/Source-Old_Confluence_PAGE.csv' (format csv, delimiter ','); COPY (SELECT CONTENTID, TITLE FROM CONTENT WHERE CONTENTTYPE='PAGE' AND PREVVER is NULL) TO '/Users/Admin/Downloads/Target_New_Confluence_PAGE.csv' (format csv, delimiter ',');

4. Save the files separately for the old and new instances after filtering any entries that might have no space name(title) associated with the ID and also filter the header column names.

   1 2 awk -F ',' '$2!="" && NR>1{print}' Source-Old_Confluence_PAGE.csv > Old_Confluence.csv awk -F ',' '$2!="" && NR>1{print}' Target_New_Confluence_PAGE.csv > New_Confluence.csv

   The next set of steps would be to map the old page ID to the new page ID and the old tinyurl to the new tinyurl from the old Confluence site to the new Confluence site. This process has to be repeated for different tables.

Steps to update map and update the Confluence page ID from the old instance to the new instance.

1. Run the following command in the same shell prompt of the node where the above two Confluence files were located. This will map the old ID to the new ID based on the page name and also generate the SQL to be run on the database to update the table. Save the output in a separate file to be run later on the database. The below example is generated for remotelink table. Also make sure to replace the placeholder '<new_conf_url>' in the below command with the new Confluence link url that we have updated in all the tables in the previous steps.

   1 2 3 awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$2],$1,$2}' New_Confluence.csv Old_Confluence.csv | awk -v sq="'" -F "," '$1!=""{print "update remotelink set URL=REPLACE(URL,"sq"<new_conf_url>/pages/viewpage.action?pageId="$2sq "," sq"<new_conf_url>/pages/viewpage.action?pageId="$1sq")" " WHERE URL="sq"<new_conf_url>/pages/viewpage.action?pageId="$2sq ";"}' > Output_rmlink_url.sql awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$2],$1,$2}' New_Confluence.csv Old_Confluence.csv | awk -v sq="'" -F "," '$1!=""{print "update remotelink set GLOBALID=REPLACE(GLOBALID,"sq$2sq "," sq$1sq ") WHERE GLOBALID like "sq"%&pageId="$2sq ";"}' > Output_rmlink_globalID.sql

2. Then run the generated file with the sql commands by connecting to the Jira database. 

   1 2 \i /home/Output_rmlink_url.sql \i /home/Output_rmlink_globalID.sql

3. (optional) You many want to repeat the steps 1 and 2 for other Jira tables which might need a update. Example: The issue description and comments might contain such Confluence urls which will have old page IDs. Remember that we already updated the Confluence url to the new url in all the tables. Repeat the below steps by substituting for the table name, column name and the '<new_conf_url>' for each of the tables in Jira as identified in Update table with url

   1 2 3 4 5 6 awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$2],$1,$2}' New_Confluence.csv Old_Confluence.csv | awk -v sq="'" -F "," '$1!=""{print "update jiraissue set description=REPLACE(description,"sq"<new_conf_url>/pages/viewpage.action?pageId="$2sq "," sq"<new_conf_url>/pages/viewpage.action?pageId="$1sq")" " WHERE description like "sq"%<new_conf_url>/pages/viewpage.action?pageId="$2 "%" sq ";"}' awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$2],$1,$2}' New_Confluence.csv Old_Confluence.csv | awk -v sq="'" -F "," '$1!=""{print "update jiraaction set actionbody=REPLACE(actionbody,"sq"<new_conf_url>/pages/viewpage.action?pageId="$2sq "," sq"<new_conf_url>/pages/viewpage.action?pageId="$1sq")" " WHERE actionbody like "sq"%<new_conf_url>/pages/viewpage.action?pageId="$2 "%" sq ";"}' .............. ..............

Steps to modify the tinyurl based on the old page ID to the tinyurl based on the new ID

This process consists of identifying the records where the tinyurl is used, decode them, then map the old ID to the new ID and convert it back to the tinyurl and update the records.

1. For the current example, we will perform the steps for the Jiraaction table. Firstly, gather all the comments where the tinyurl is used. Make sure to replace the placeholder '<new_conf_url>' with the new Confluence url. For Postgres

   1 COPY (select actionbody from jiraaction WHERE actionbody like '%<new_conf_url>/x/%') TO '/Users/Admin/Downloads/comments.csv' (format csv, delimiter ',');

2. Get all the unique tinyurls, in the comments, gathered from the comments table and output to a file by name ToBeDecoded.csv. We assume the base64 encoded page ID would be between 3 and 12 characters in length.

   1 grep -Eow "https://confluence-meddev.oneabbott.com/x/\w{3,12}" comments.csv | sort | uniq | awk -F "/" '{print $NF","$0}' > ToBeDecoded.csv

3.  Create a python script urldecode.py to decode the base64 decoded tiny urls to their original page ID. Save the file as urldecode.py. Also notice that the file to be parsed was the ToBeDecoded.csv file, we created in the previous step.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 from __future__ import print_function import base64 import struct import csv infile = open('ToBeDecoded.csv', 'r') lines = infile.readlines() for line in lines: sline = line.strip() #Get rid of trailing newline characters at the end of the line sline = sline.split(',') #Separate into columns based on comma page_short_id = sline[0] #The first column is the page ID tinyurl=page_short_id.replace("/", "\n").replace("-", "/").replace("_", "+") #Replace characters to sanitize the input padded_id = tinyurl.ljust(11, "A") + "=" #Introduce padding for a successful base64 decode decoded_id = base64.b64decode(padded_id) PageID = struct.unpack("L", decoded_id)[0] #Unpack the packed binry data print (PageID,sline[1],sep=",") infile.close()

4. Run the python script to generate a file with the decoded page ID. You can utilize either python3 or python to run the code, depending on version of python installed in your environment.

   1 2 3 python3 urldecode.py > OldPageID_Tinyurl.csv ---Alternatively python urldecode.py > OldPageID_Tinyurl.csv

5. Compare the generated page IDs from the above step after base64 decode and generate the corresponding page name using the Old_Confluence.csv file. Save it as a new file OldLinkFile_WithPageName.csv for comparison

   1 cat OldPageID_Tinyurl.csv | while read line ;do OldID=$(echo $line | awk -F "," '{print $1}'); grep $OldID Old_Confluence.csv; done > OldLinkFile_WithPageName.csv

6. Now map the old id to the new id in the new Confluence instance, based on page name. Utilize the above file, OldLinkFile_WithPageName.csv and the New_Confluence.csv file we generated before. Save the output to a new file PageMappedFile.csv

   1 awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$2],$1,$2}' New_Confluence.csv OldLinkFile_WithPageName.csv > PageMappedFile.csv

7. Now we shall decode the new page ID to generate the new tinyurl. For that purpose, we shall create a file consisting of the old page ID, the old tinyurl and the new page ID and save it to a file ToGenerate_New_TinyUrl.csv

   1 awk -F ',' -v OFS="," 'NR==FNR{a[$2] = $1;next;}{print a[$1],$2,$1}' PageMappedFile.csv OldPageID_Tinyurl.csv | sed -e 's/ //g' > ToGenerate_New_TinyUrl.csv

8. Now we shall encode the new tinyurl based on the already available existing KB How to programmatically generate the Tiny link of a Confluence page. We shall modify it a bit to parse our file and provide the desirable output. Save the below code as ChangeID.pl. Ensure to substitute the '<new_conf_url>' in the code with the new Confluence url. We will be using the above generated ToGenerate_New_TinyUrl.csv file.   

   Encode page ID to generate Tinyurl

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 use MIME::Base64 qw(encode_base64); my $connieBaseUrl = '<new_conf_url>'; ### Confluence Base URL my $file = 'ToGenerate_New_TinyUrl.csv'; open my $info, $file or die "Could not open $file: $!"; while( my $line = <$info>) { my @page = split(",",$line); my $pageID = @page[0]; $pageID =~ s/^\s+|\s+$//g; my $tinyString = encode_base64(pack("L", $pageID)); ### the page ID must be encoded after converting it to a byte array my $actualTinyString = ''; my $padding = 0; foreach my $c (split //, $tinyString) { if ($c eq '=') { next; } if ($padding == 1 && $c eq 'A') { next; } $padding = 0; if ($c eq '/') { $actualTinyString .= '-'; } elsif ($c eq '+') { $actualTinyString .= '_'; } elsif ($c eq "\n") { $actualTinyString .= '/'; } else { $actualTinyString .= $c; } } my $tinyUrl = $connieBaseUrl . '/x/' . $actualTinyString; $line =~ s/^\s+|\s+$//g; print $tinyUrl.",".@page[1] . "\n"; } close $info;

9. Run the below command to generate the sql commands needed to update the comments which have old tinyurl with the new tinyurl. We will be using the 'ChangeID.pl' file we created above

   1 perl ChangeID.pl | awk -v sq="'" -F "," '{print "update jiraaction set actionbody=REPLACE(actionbody,"sq$2sq","sq$1sq")" " WHERE actionbody like " sq "%" $2 "%" sq ";"}' > updatesql_comment.sql

10. Then finally run the above generated sql file to update the jiraaction table with the updated tinyurl.  For Postgres

    1 \i /~home/updatesql_comment.sql

Repeat the step 9 and 10 for every other jira table that requires the update, after substituting for the table name, column name in the step 9.