How to Bulk Delete Blogposts of a Confluence Space Manually from Confluence Database Using SQL Commands
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
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.
Purpose
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:
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.
Resolution
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
To delete blogposts of a specific Confluence Space in your Confluence instance, you may execute the following queries in your Confluence database.
SELECT spaceid
FROM SPACES
WHERE spacename = '<AffectedSpaceName>';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
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE PREVVER IN (SELECT CONTENTID from IDTODELETE));DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));DELETE FROM LINKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT_LABEL
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM EXTRNLNKS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM IDTODELETE);DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM CONTENT WHERE PREVVER IN(SELECT CONTENTID FROM IDTODELETE));DELETE FROM NOTIFICATIONS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE PREVVER IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE contenttype = 'BLOGPOST'
AND spaceid = '<SpaceID>';