How to Bulk Delete Archive Emails from Confluence Database
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 archived emails tied to a Confluence Space. This is an alternative method should deleting archived emails from the Confluence UI fail, e.g., due to the large number of archived emails stored in the Database.
Delete Mail attachment in Confluence and the related file stored in the file system.
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 bulk delete archive emails of a specific 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 MySQL Confluence DB to store all of the Archiving Mails information
1
2
3
4
CREATE TABLE IDTODELETE AS SELECT CONTENTID
FROM CONTENT WHERE contenttype = 'CUSTOM'
AND pluginkey = 'com.atlassian.confluence.plugins.confluence-mail-archiving:mail'
AND spaceid = '<SpaceIDIdentifiedFromThePreviousQuery>';Deleting related information of the Archiving Mails stored in CONTENTPROPERTIES table
1
2
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));Deleting related information of the Archiving Mails stored in IMAGEDETAILS table
1
DELETE FROM IMAGEDETAILS WHERE ATTACHMENTID IN(SELECT CONTENTID FROM CONTENT WHERE CONTENTTYPE = 'ATTACHMENT' AND PAGEID IN (SELECT CONTENTID from IDTODELETE));Deleting the attachments of Archiving Mails records from the CONTENT table
1
2
3
DELETE FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);Deleting the Archiving Mails records from the CONTENTPROPERTIES table
1
2
DELETE FROM CONTENTPROPERTIES
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);Deleting the Archiving Mails records from the BODYCONTENT table
1
2
DELETE FROM BODYCONTENT
WHERE CONTENTID in (SELECT CONTENTID FROM IDTODELETE);Deleting the Archiving Mails records from the NOTIFICATIONS table
1
2
DELETE FROM NOTIFICATIONS
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);Deleting the Archiving Mails records from the CONTENT table
1
2
3
4
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
Listing all the attachment ID of the attachments that is attached to Mail Archives
1
2
3
SELECT contentid FROM CONTENT
WHERE CONTENTTYPE = 'ATTACHMENT'
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);Delete the Mail attachment using REST API. get the <Content ID> listed from the first step.
1
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.
Was this helpful?