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

Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.

Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.

*Except Fisheye and Crucible

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:

(Auto-migrated image: description temporarily unavailable)

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.

    ℹ️ Please do take note of the Application Link ID and Application Name's values.

    Example

    Sample Storage Format

    1 <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

    1  <ac:parameter ac:name="serverId">79f0263c-a3a5-323b-870c-aea1895cabeb</ac:parameter>

    Application Name

    1  <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:

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

    Example

    (Auto-migrated image: description temporarily unavailable)

    Application Link ID

    1  <id>144880e9-a353-312f-9412-ed028e8166fa</id>

    Application Name

    1  <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 viewing the Storage Format of the affected pages.

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

ℹ️ Replace 'KEY' with the affected space key and 'Page Title' with the affected page title.

  • Search for something like this:

1 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 1

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

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

1 2 3 <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>

If the affected instance is on Confluence 7 or newer, make sure to update the Drafts to use changes on the next opening of the draft, If on the older Confluence version, skip to the update of BODYCONTENT:

1 UPDATE CONTENTPROPERTIES set stringval='synchrony-recovery' WHERE PROPERTYID in (SELECT cp.PROPERTYID FROM BODYCONTENT bc JOIN CONTENTPROPERTIES cp ON cp.CONTENTID = bc.CONTENTID WHERE bc.BODY like '%<old_string>%' AND cp.PROPERTYNAME = 'sync-rev-source');

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

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

Or this query for MSSQL:

1 2 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 back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

  1. For Confluence 6.x and higher with the Collaborative Editing set to ON or Limited, Turn Collaborative Editing Offbefore 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.

    Per Administering Collaborative Editing, it's a good idea to prompt your users to publish any shared drafts before you turn collaborative editing off, as they will not be able to resume editing existing shared drafts or unpublished changes.

  2. Shutdown Confluence

  3. Execute the following SQL update statement:

  4. 1 2 3 4 5 6 7 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>');
  5. 1 2 3 4 5 6 7 8 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 ServerID</ac:parameter>', '<ac:parameter ac:name="serverId">Atlassian Jira ServerID</ac:parameter>') as ntext);

  6. 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

    1 2 3 4 5 6 7 8 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'));

  7. You may wish to verify that the content in the database has been updated before/after your UPDATE query has been set. Please engage with your DBA if needed, but you can select using the criteria below:

    1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT FROM CONTENTPROPERTIES WHERE PROPERTYID in ( SELECT cp.PROPERTYID FROM BODYCONTENT bc JOIN CONTENTPROPERTIES cp ON cp.CONTENTID = bc.CONTENTID WHERE bc.BODY like '%<ac:parameter ac:name="serverId">the-id-of-the-old-applink</ac:parameter>%' );

  8. Restart Confluence

Resolution 2

When migrating to a new Jira DC Instance and deleting the old app link, ensure that the new app link is created with the same name as the previous one.This approach simplifies the migration process and is more efficient compared to Resolution 1, which involves running SQL scripts.

By maintaining the same app link name, you can avoid manual database modifications and ensure a smoother transition.

Updated on April 11, 2025
Platform
Data Center only
Product
Confluence Data Center
On this pageProblemDiagnosisCausesResolution 1Resolution 2

Still need help?

The Atlassian Community is here for you.
Ask the Community