How to bulk update JIRA Issue Macro to point to a different JIRA instance

Still need help?

The Atlassian Community is here for you.

Ask the community

This article only applies to Atlassian's server products. Learn more about the differences between cloud and server.

Problem

The Jira Issues Macro is used to create links to Jira issues in Confluence pages.  These links contain the identification of which Jira instance the link refers to.

In some situations, (e.g. domain change, migration, merge instance, etc) it will be necessary to edit these links and point them to a different Jira instance. Otherwise, the following error message will be shown on the Confluence page:

Diagnosis

A Jira Issue Macro link contains two identifiers; The Application Link ID and the Application Name. Should you encounter this issue, it is necessary to check if the Application Link ID and the Application Name is pointing to the correct Jira instance.

You may do so by doing the following:

  1. First, please access the affected page and view the Confluence Storage Format.
    (info) Please do take note of the Application Link ID and Application Name's values.

    Example
    Sample Storage Format
    <p><ac:structured-macro ac:name="Jira" ac:schema-version="1" ac:macro-id="f4b5962b-5cf2-4754-90d9-758306583d18"><ac:parameter ac:name="server">Jira Old</ac:parameter><ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter><ac:parameter ac:name="maximumIssues">20</ac:parameter><ac:parameter ac:name="jqlQuery">project = SP </ac:parameter><ac:parameter ac:name="serverId">79f0263c-a3a5-323b-870c-aea1895cabeb</ac:parameter></ac:structured-macro></p>
    Application Link ID
     <ac:parameter ac:name="serverId">79f0263c-a3a5-323b-870c-aea1895cabeb</ac:parameter>
    Application Name
     <ac:parameter ac:name="server">Jira Old</ac:parameter>
  2. Next, please identify the correct Application Link ID and Application Name by accessing the following URL from your web browser:

    http://<Jira-Base-URL>/rest/applinks/1.0/manifest
    Example

    Application Link ID
     <id>144880e9-a353-312f-9412-ed028e8166fa</id>
    Application Name
     <name>Atlassian Jira</name>
  3. Lastly, we then need to match the Application Link ID and the Application Name identified from both Step 1 and Step 2.

Another method to finding the IDs and links
  • Find 2 Jira Issue macros (ideally on the same page, but it can be in two different ones). Remember the space key and page titles.
  • Fix one of the Jira Issue macros, by editing it and researching the JQL (click the magnifying glass icon)
    • Now we have 1 fixed issue and one linking to the old server
    • After that, one will be linking to the source Jira while the other will be fixed, linking to the current Jira
  • Check the XML for the page(s) by either executing the following SQL query (you will need to run once for each page) or to view the Storage Format of the affected pages.


select body from bodycontent where contentid = (select contentid from content where spaceid = (select spaceid from spaces where spacekey = 'KEY') and title = 'Page Title');


(info) Replace 'KEY' for the affected space key and 'Page Title' for the affected page title.

  • Search for something like this:
ac:name="server">Jira (source.atlassian.net)</ac:parameter><ac:parameter ac:name="serverId">67806a6b-f924-3896-8e84-fecf05e0398b
  • This example is for the source server (the Jira Issue Macro that is not fixed), but the other one is the same thing.
  • In 'serverId', copy the server ID for both the source and destination

Causes

In this case, the issue occurs as the Application Link ID and the Application Name in the affected's page Storage Format and the target Jira instance is different. 

Resolution

In this example, we will move the link of a Jira Issue Macro from Application Link ID 79f0263c-a3a5-323b-870c-aea1895cabeb and Name Jira Old to Application Link ID 144880e9-a353-312f-9412-ed028e8166fa and Name Atlassian Jira.

All Jira Issue Macro information is stored in the BODYCONTENT table, as in the following example:

<p><ac:structured-macro ac:name="Jira"><ac:parameter ac:name="columns">key,summary,type,created,updated,due,assignee,reporter,priority,status,resolution</ac:parameter>
<ac:parameter ac:name="server">Jira Old</ac:parameter><ac:parameter ac:name="serverId">79f0263c-a3a5-323b-870c-aea1895cabeb</ac:parameter>
<ac:parameter ac:name="key">TST-1</ac:parameter></ac:structured-macro></p>

The following SQL statement will be used to update the information into the BODYCONTENT table:

UPDATE BODYCONTENT 
 SET BODY = REPLACE(BODY,'<old_string>','<new_string>');

Or this query for MSSQL:

UPDATE BODYCONTENT
 SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)),'<old_string>','<new_string>') as ntext);

Where <old_string> is the string to search on the field BODY, which will be replaced by <new_string>.


Always backup your data before performing any modifications to the database.

  1. Shutdown Confluence

  2. Execute the following SQL update statement:

    SQL
    UPDATE BODYCONTENT 
    SET BODY = REPLACE(BODY,'<ac:parameter ac:name="server">Jira Old</ac:parameter>','<ac:parameter ac:name="server">Atlassian Jira</ac:parameter>');
    
    UPDATE BODYCONTENT 
    SET BODY = REPLACE(BODY,'<ac:parameter ac:name="serverId">79f0263c-a3a5-323b-870c-aea1895cabeb</ac:parameter>','<ac:parameter ac:name="serverId">144880e9-a353-312f-9412-ed028e8166fa</ac:parameter>');
    MS SQL
    UPDATE BODYCONTENT
     SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)),'<ac:parameter ac:name="server">Jira Old</ac:parameter>',
    '<ac:parameter ac:name="server">Atlassian Jira</ac:parameter>') as ntext);
    
    
    UPDATE BODYCONTENT
     SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)),'<ac:parameter ac:name="serverId">Jira Old's ServerID</ac:parameter>',
    '<ac:parameter ac:name="serverId">Atlassian Jira's ServerID</ac:parameter>') as ntext);

    You can also add conditions, in case you don't want all pages to be affected. The following SQL query will only update contents from the Space with spacekey - 'KEY':

    SQL update queries by Spacekey value

    UPDATE bodycontent SET body = replace(body, 'ac:name="server">Jira (source.atlassian.net', 'ac:name="server">Jira (destination.atlassian.net') where contentid in (select contentid from content where spaceid in (select spaceid from spaces where spacekey = 'KEY'));


    UPDATE bodycontent SET body = replace(body, 'ac:name="serverId"><source server ID>', 'ac:name="serverId"><destination server ID>') where contentid in (select contentid from content where spaceid in (select spaceid from spaces where spacekey = 'KEY'));
  3. Restart Confluence

For Confluence 6.x with the Collaborative Editing set to ON or Limited, Turn Collaborative Editing Off before executing the database manipulation above and turn it back on once the Confluence has restarted after the change to ensure the Synchrony Cache gets the replaced value.

Confluence Administrator shall also ensure no users should attempt to edit any pages during the period where Collaborative Editing is off to avoid losing unpublished changes.



Description

The Jira Issues Macro is used to create links to Jira issues in Confluence pages.  These links contain the identification of which Jira instance the link refers to.

In some situations, (e.g. domain change, migration, merge instance, etc) it will be necessary to edit these links and point them to a different Jira instance. Otherwise, an error message will be shown on the Confluence page.

Product Confluence
Platform Server
Last modified on Dec 18, 2018

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.