How to Bulk Delete Archive Emails from Confluence Database

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.

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

  1. 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. 
  2.  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.

Identify the SpaceID of the affected Confluence Space
SELECT spaceid 
FROM SPACES 
WHERE spacename = '<AffectedSpaceName>';
Create temporary table in your MySQL Confluence DB to store all of the Archiving Mails information
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
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
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
DELETE FROM CONTENT 
WHERE CONTENTTYPE = 'ATTACHMENT' 
AND PAGEID IN (SELECT CONTENTID FROM IDTODELETE);
Deleting the Archiving Mails records from the CONTENTPROPERTIES table
DELETE FROM CONTENTPROPERTIES 
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);
Deleting the Archiving Mails records from the BODYCONTENT table
DELETE FROM BODYCONTENT 
WHERE CONTENTID in (SELECT CONTENTID FROM IDTODELETE);
Deleting the Archiving Mails records from the NOTIFICATIONS table
DELETE FROM NOTIFICATIONS 
WHERE CONTENTID IN (SELECT CONTENTID FROM IDTODELETE);
Deleting the Archiving Mails records from the CONTENT table
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
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.
curl -v -S -u admin:admin -X DELETE http://<host name>:<Port>/confluence/rest/api/content/<Content ID> | python -mjson.tool

After 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.

 

Last modified on Sep 20, 2016

Was this helpful?

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