How to bulk update Confluence content through the database to replace old macros with new ones

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform Notice: Server and Data Center Only - This article only applies to Atlassian products on the server and data center platforms.

Summary

There might be cases when the Confluence administrator may need to replace a specific macro with a new one.

Currently, Confluence doesn't support bulk update of pages from the UI, as highlighted in the following feature request: CONFSERVER-56085 - Getting issue details... STATUS .

The following procedure provides the steps to bulk update content directly on the database, replacing the legacy macro with the new one.
This procedure only works if the old and new macros share the same attributes and configuration, having only the name changed.
If there's a major change on the macro attributes, then you may use this as a starting point.


This KB article provides changes that are out of the scope of the Atlassian Support and, therefore, is provided as-is.

Since it suggests changes on the database, it is strongly recommended to validate it on a test environment before applying it on production.

Make sure to have a backup of the database before applying changes on production.


Solution

Preparation

The first thing you need to do is to determine the name of the macro you want to replace on the target pages. You will also need the name of the new macro.
You can get this information by accessing the Storage Format of pages using the target macros.
The image below shows an example with the warning macro.


You may also need to get a report on the pages using the target macro. The following SQL query provides this information from latest (current) version of pages using the old macro. This is targeting pages, blogposts and page comments.
Change <OLD MACRO NAME> for your target macro as retrieved from the storage format.


SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
	ON c.contentid = bc.contentid
JOIN SPACES s
	ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
	AND c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT')
	AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%';


If Collaborative editing is enabled, we also need to touch the shared draft of target pages.
The following SQL query provides information on shared drafts (pages and blogposts) using the old macro.

SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
	ON c.contentid = bc.contentid
JOIN SPACES s
	ON c.spaceid = s.spaceid
WHERE c.content_status = 'draft'
	AND c.contenttype IN ('PAGE', 'BLOGPOST')
	AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%';



Change Procedure

  1. Stop Confluence following your standard procedure.
    1. If running Confluence Data Center, stop Confluence on all nodes.
  2. If Collaborative Editing is enabled and Synchrony is running on a standalone cluster, stop the Synchrony process on each node.
  3. If Collaborative Editing is enabled, change the properties of target shared drafts so that Synchrony updates its cache with the modified content.

    update CONTENTPROPERTIES
    set stringval = 'synchrony-recovery'
    where CONTENTID IN (SELECT c.contentid
      FROM CONTENT c
      JOIN BODYCONTENT bc
      	ON c.contentid = bc.contentid
      JOIN SPACES s
    	ON c.spaceid = s.spaceid
      WHERE c.content_status = 'draft'
    	AND c.contenttype IN ('PAGE', 'BLOGPOST')
    	AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%'
    )
       AND propertyname = 'sync-rev-source'
    ;
  4. Run the following SQL query to update the content of Pages, Blogposts and Page Comments.

    To update only the latest version of target content...
    UPDATE bodycontent
    SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') 
    WHERE bodycontentid in (
      SELECT bc.bodycontentid
      FROM CONTENT c
      JOIN BODYCONTENT bc
          ON c.contentid = bc.contentid
      WHERE c.prevver IS NULL
        AND c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT')
        AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%'
    )
    ;
    To update all versions of target content...
    UPDATE bodycontent
    SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') 
    WHERE bodycontentid in (
      SELECT bc.bodycontentid
      FROM CONTENT c
      JOIN BODYCONTENT bc
          ON c.contentid = bc.contentid
      WHERE c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT')
        AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%'
    )
    ;
  5. If Collaborative Editing is enabled, update the body of shared drafts.

    UPDATE bodycontent
    SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') 
    WHERE bodycontentid in (
      SELECT bc.bodycontentid
      FROM CONTENT c
      JOIN BODYCONTENT bc
          ON c.contentid = bc.contentid
      WHERE c.content_status = 'draft'
        AND c.contenttype IN ('PAGE', 'BLOGPOST')
        AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%'
    )
    ;
  6. If Collaborative Editing is enabled and Synchrony is running on a standalone cluster, start the Synchrony process on each node following your standard procedure.
  7. Start Confluence following your standard procedure.
    1. If running Confluence Data Center, start Confluence on each node at a time.


See Also

How to find all pages and spaces that use a specific macro via SQL

How Do Drafts Work on Confluence


Last modified on Dec 23, 2020

Was this helpful?

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