Remove Orphaned Topics and Private Messages in phpBB

Published by Torry Crass on

I recently had to clear out a slew of accounts from a phpBB installation.  In the process some private messages were not removed since I handled a lot of the removal process manually rather than through the phpBB interface.  As such, I needed a way to remove the orphaned private messages and posts.  I thought I would share that code here:

BEWARE: THIS CAN DELETE YOUR VALID DATA.  MAKE SURE YOU HAVE A VALID BACKUP BEFORE USING THIS CODE!

DELETE
FROM phpbb_privmsgs
WHERE `msg_id`
NOT IN (
SELECT `msg_id`
FROM phpbb3_privmsgs_to
)

Tada!  Orphaned private messages have been erased.  This worked fine for my purposes but you need to evaluate this for your own needs and make sure it won't trash anything you really do want to keep.

Next I ran the following queries to get rid of extra orphaned posts and information:

This removes posts where the spammer was the only poster.

DELETE
FROM `phpbb_posts`
WHERE topic_id
NOT IN (
SELECT topic_id
FROM phpbb_topics
)

This will remove entries in the posted table related to missing topics

DELETE
FROM `phpbb_topics_posted`
WHERE topic_id
NOT IN (
SELECT topic_id
FROM phpbb_topics
)

Removes orphaned entries out of the tracking table

DELETE
FROM `phpbb_topics_track`
WHERE topic_id
NOT IN (
SELECT topic_id
FROM phpbb_topics
)

Removes orphaned entries out of the watch table

DELETE
FROM `phpbb_topics_watch`
WHERE topic_id
NOT IN (
SELECT topic_id
FROM phpbb_topics
)

Removes items from topic watch not associated with users.

DELETE
FROM `phpbb_topics_watch`
WHERE user_id
NOT IN (
SELECT user_id
FROM phpbb_users
)

Removes items from the track table not associated with users

DELETE
FROM `phpbb_topics_track`
WHERE user_id
NOT IN (
SELECT user_id
FROM phpbb_users
)

Removes items from the posted table not associated with users

DELETE
FROM `phpbb_topics_posted`
WHERE user_id
NOT IN (
SELECT user_id
FROM phpbb_users
)

Now the next part gets a bit nasty.  Now you have removed all the orphaned information but your posts will still reflect that you have information there.  This will render some portions of your forum inoperable.  As a result, you need to do some serious picking through your site to determine how to approach this.  I did the following:

I found manually edited the database entries for the forums and set the current post information to blanks and 0's

I then ran the following query to find what topics needed to be updated:

SELECT *
FROM `phpbb_topics`
WHERE topic_last_poster_id
NOT IN (
SELECT user_id
FROM phpbb_users
)

Next, I made a custom update query to set the necessary entries to 0 and blank for those topics.  This will unfortunately cause some residual trouble with the topics on the site until people post the next entries, but at least there won't be bogus names and topic id's floating around.

UPDATE `phpbb_topics`
SET topic_last_poster_id = "0", topic_last_post_id = "0", topic_last_poster_name = NULL, topic_last_post_subject = NULL, topic_last_post_time = 0
WHERE topic_last_poster_id
NOT IN (
SELECT user_id
FROM phpbb_users
)

Next, I found it was getting a bit testy with the amount of SQL editing needed to continue so I logged into the site with moderator credentials and went to each forum and did a "re-sync" which seemed to resolve the rest of the trouble and do the same for the overall forums in the ACP area.

Total cleanup time for this was around 7 hours by the time all was said and done.  Good luck and happy filtering.
 

Categories: Technical

0 Comments

Leave a Reply