How to Bulk Delete Blogposts of a Confluence Space Manually from Confluence Database Using SQL Commands
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
Summary
If you are unsure how to deal with the database, contact your DBA. Make sure to have the database backed up completely before going further. These SQL commands were tested in some environments and they worked as intended.
However, it might not work in specific cases and newer versions of confluence as new constraints as changes may be done to confluence database structure. As such, a database backup is mandatory in case any issue arises and you'd need to rollback to the previous working state of Confluence Database.
This KB outlines how to bulk delete blogposts in a Confluence Space manually from Confluence Database should it not be possible to delete them from Confluence UI itself, e.g. due to the large number of blogposts, the deletion process is then interrupted as it had reached the maximum threshold. shown in the log snippet below:
1
2
3
2016-08-09 16:13:50,579 WARN [Long running task: Space removal long running task] [confluence.util.profiling.DurationThresholdWarningTimingHelperFactory] logMessage Execution time for publishing event com.atlassian.confluence.event.events.content.blogpost.BlogPostRemoveEvent[source=com.atlassian.confluence.pages.DefaultPageManager@7ae889c] took 5711 ms (warning threshold is 5000 ms)
-- referer: <Confluence-Base-URL>/spaces/removespace.action?key=ConfSupport | url: /spaces/doremovespace.action | userName: admin | action: doremovespace | space: 18808910
2016-08-09 16:14:42,909 WARN [Long running task: Space removal long running task] [confluence.util.profiling.DurationThresholdWarningTimingHelperFactory] logMessage Execution time for publishing event com.atlassian.confluence.event.events.content.blogpost.BlogPostRemoveEvent[source=com.atlassian.confluence.pages.DefaultPageManager@7ae889c] took 5829 ms (warning threshold is 5000 ms)Before you proceed to the Resolution section, please do take note that this is an alternative method, should deleting blogposts from Confluence UI fails.
Please do try to first delete the blog posts through Confluence REST API content-delete function before then proceeding with the DB manipulation below.
Solution
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.
To delete blogposts of a specific Confluence Space in your Confluence instance, you may execute the following queries in your Confluence database.
Identify the SpaceID of the affected Confluence Space
1
2
3
SELECT spaceid
FROM SPACES
WHERE spacename = '<AffectedSpaceName>';Create temporary table in your Confluence DB to store all of the ContentID of the related blogposts
1
2
3
CREATE TABLE IDTODELETE AS SELECT CONTENTID
FROM CONTENT WHERE contenttype = 'BLOGPOST'
AND spaceid = '<SpaceID>';To delete the blogposts together all of its relevant data from the DB
1
2
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));1
2
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID from IDTODELETE));1
DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));1
2
DELETE FROM LINKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM CONTENT_LABEL
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM EXTRNLNKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);1
2
3
DELETE FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM CONTENT WHERE PREVVER IN(SELECT CONTENTID FROM IDTODELETE));1
2
DELETE FROM NOTIFICATIONS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);1
2
DELETE FROM CONTENT
WHERE PREVVER IN (SELECT CONTENTID FROM IDTODELETE);1
2
3
DELETE FROM CONTENT
WHERE contenttype = 'BLOGPOST'
AND spaceid = '<SpaceID>';Was this helpful?