How to Bulk Delete Archive Emails from Confluence Database
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the server and data center platforms.
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 outlined how to bulk delete archived mails tied to a Confluence Space. This is an alternative method, should deleting archived mails from Confluence UI fails e.g. due to the large number of archived mails stored in the Database.
- Delete Mail attachment in Confluence and the related file stored in the file system
Resolution
Always back up your data before performing any modification to the database. If possible, try your modifications on a test server.
To bulk delete archive emails of a specific 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 = 'CUSTOM'
AND pluginkey = 'com.atlassian.confluence.plugins.confluence-mail-archiving:mail'
AND spaceid = '<SpaceIDIdentifiedFromThePreviousQuery>';DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID 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 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 NOTIFICATIONS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);DELETE FROM CONTENT
WHERE contenttype = 'CUSTOM'
AND pluginkey = 'com.atlassian.confluence.plugins.confluence-mail-archiving:mail'
AND spaceid = '<SpaceIDIdentifiedFromTheFirstQuery>';
To delete Mail attachment in Confluence and the related file stored in the file system
SELECT contentid FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);curl -v -S -u admin:admin -X DELETE http://<host name>:<Port>/confluence/rest/api/content/<Content ID> | python -mjson.toolAfter executing the REST API, purge the deleted attachment in the trash and it will delete the attachment from the file system. Navigate to Space tools >> Content and Tools >> Trash.