XF 2.2 How I Purged old Conversations in database

[ActorMike]

I simply picked a conversation ID that was in the timeframe I wanted and executed this query. In this case the id was 17783.

DELETE FROM databasename.xf_conversation_message WHERE (conversation_id <= '17783');
DELETE FROM databasename.xf_conversation_master WHERE (conversation_id <= '17783');
DELETE FROM databasename.xf_conversation_recipient WHERE (conversation_id <= '17783');
DELETE FROM databasename.xf_conversation_user WHERE (conversation_id <= '17783');

FYI-
Database size before 265,667 KB
Database size after 242,352 KB

 

[V3NTUS]

Quite useful, thanks!

 

[webbouk]

I'd be wary about purging old conversations. I did it once, picked a date in the past, bearing in mind our forum has been running for 22 years, and received a deluge of complaints from some long-standing members who'd collected numerous messages over time as quick references, info, etc, and suddenly found them all vanished.
Luckily I had a backup of the tables so managed to reinstate them.

If you are going to do it give plenty of notice to your members first

 

[ActorMike]

I'd be wary about purging old conversations. I did it once, picked a date in the past, bearing in mind our forum has been running for 22 years, and received a deluge of complaints from some long-standing members who'd collected numerous messages over time as quick references, info, etc, and suddenly found them all vanished.
Luckily I had a backup of the tables so managed to reinstate them.

If you are going to do it give plenty of notice to your members first

Our forum has been running since 2001. Not one person said a peep after I purged them. Probably depends on what kind of forum you run, but our goal is to provide public information that is indexed on Google, not long-term private storage for people to do things behind the scenes and take up resources.

 

[webbouk]

Well done you

 

[V3NTUS]

It would also be cool if we had a way to delete old attachments, which may take even bigger space though. Nonetheless I agree with ActorMike, reducing the database size would benefit all users by making everything run faster as well, so why not? Unless they think our forums are a mailing service. On a side note, they could still access their mails indeed, if any, as when you have a conversation with someone, by default you also receive an email with the text of the conversation, which would help them retrieve the contents eventually. Not my case as well, though. Nobody complained.

 

[MentaL]

Delete private conversations in bulk.

Rudy submitted a new resource: Delete private conversations in bulk. - Bulk private conversations got you down? Here's how you can clean out those inboxes. Did a staff member accidentally send out a bulk private conversation to the forum membership? That happened with us. As a result, we had...

xenforo.com

Some other ways to bulk delete.

 

[lucien_lies]

there is a problem with reactions in private messages

 

[Renegade]

So I wanted to purge the old conversations and landed up here among other threads. I know this is an old thread but leaving the solution here which worked for me and does not require an add-on.

-- (Optional) Disable foreign key checks if necessary:
SET FOREIGN_KEY_CHECKS=0;

-- Define the threshold timestamp for January 1, 2015.
SET @threshold = 1420070400;

-- Delete recipients for conversations older than Jan 2015.
DELETE cr
FROM xf_conversation_recipient cr
JOIN xf_conversation_master cm ON cr.conversation_id = cm.conversation_id
WHERE cm.start_date < @threshold;

-- Delete conversation user records.
DELETE cu
FROM xf_conversation_user cu
JOIN xf_conversation_master cm ON cu.conversation_id = cm.conversation_id
WHERE cm.start_date < @threshold;

-- Delete conversation messages.
DELETE cmess
FROM xf_conversation_message cmess
JOIN xf_conversation_master cm ON cmess.conversation_id = cm.conversation_id
WHERE cm.start_date < @threshold;

-- Finally, delete conversation master records.
DELETE FROM xf_conversation_master
WHERE start_date < @threshold;

-- (Optional) Re-enable foreign key checks:
SET FOREIGN_KEY_CHECKS=1;

 

[ActorMike]

Good reminder for me to run this again! Reduces my database by 10%