As you may have noticed, the SearchTempest forums use phpBB. Generally problems I’ve had with it are due to my own ignorance and today was no exception!

I was messing with the forum permissions, and I accidentally gave anonymous guest users the power to ‘bump’ topics. In phpbb, there’s a special function to bump a topic without having to post to it. Essentially it automatically updates the date of the latest post in that topic to the current time, so it appears as if it was just posted, and the topic moves to the top of the forum. (A little ‘Last bumped by [username]’ line appears at the bottom of the post, but I didn’t notice it at first.)

The problem is, apparently there are bots out there that will just go around bumping your posts like crazy if you let them. I’m not sure what they have to gain by this.. possibly just anarchy. Anyway, my temporarily lax user permissions resulted in a whole pile of topics from circa 2008 being bumped to present times. And since the bumping process actually changes the dates of the topic and its most recent post, there’s no easy way to just ‘unbump’ them. I didn’t relish the idea of manually going through my database editing the timestamps of tens or hundreds of posts, soooo I came up with a better solution.

(It probably took me just as long, but I learned some MySQL subtleties along the way, and now I can pass it along, in case anyone else runs into the same problem!) Without more ado, the code to automatically ‘unbump’ phpbb topics:

This code is tested on phpBB 3.0.9.

USE `phpbb`; #set to the name of your phpbb database
SET @from_date=0;
SET @age_when_bumped=604800; #604800 seconds = 1 week
SET @post_margin = 600; #10 minutes

#Use only one of the next two CREATE TEMPORARY TABLE lines, depending on whether you want to unbump only anonymous bumps, or all bumps
#This line causes only anonymous posts to be unbumped
CREATE TEMPORARY TABLE phpbb_remove_bumps_temp SELECT topic_id, topic_last_post_time, topic_time FROM phpbb_topics WHERE topic_bumped=1 AND topic_bumper=1 AND topic_last_post_time >= @from_date AND topic_last_post_time - topic_time >= @age_when_bumped;

#This line causes ALL bumps to be un-bumped, not just by anonymous
#CREATE TEMPORARY TABLE phpbb_remove_bumps_temp SELECT topic_id, topic_last_post_time, topic_time FROM phpbb_topics WHERE topic_bumped=1 AND topic_last_post_time >= @from_date AND topic_last_post_time - topic_time >= @age_when_bumped;

#need to create second temp table because can't access the same temp table twice in a single MySQL query (which we would need in the update statement below)
CREATE TEMPORARY TABLE phpbb_new_times_temp SELECT topic_id, topic_last_post_time, topic_time AS new_time FROM phpbb_remove_bumps_temp;

UPDATE phpbb_new_times_temp nt SET new_time = (SELECT MAX(post_time) FROM phpbb_posts WHERE topic_id = nt.topic_id AND post_time != nt.topic_last_post_time) + @post_margin WHERE (SELECT count(*) FROM phpbb_posts WHERE topic_id = nt.topic_id) > 1;

UPDATE phpbb_posts SET post_time = (SELECT new_time FROM phpbb_new_times_temp WHERE topic_id = phpbb_posts.topic_id) WHERE (topic_id,post_time) in (SELECT topic_id, topic_last_post_time FROM phpbb_remove_bumps_temp);

UPDATE phpbb_topics SET topic_last_post_time = (SELECT new_time FROM phpbb_new_times_temp WHERE topic_id = phpbb_topics.topic_id), topic_bumped=0, topic_bumper=0 WHERE topic_id in (SELECT topic_id FROM phpbb_remove_bumps_temp);

This is straight MySQL code, so basically you just need to log into MySQL (either through a terminal shell, or phpMyAdmin for example), with permissions to your phpbb database. Then just copy the code in and execute it. By default it goes through and looks for topics bumped by anonymous users, which were more than one week old when bumped. It then resets the date of the last post in that topic, and of the topic’s ‘last post date’ to be a short time after the second-to-last post. Or, if there was only one post in the topic, it sets the date of that post back to the start date for the topic, as it was originally. Finally, it clears the topic’s topic_bumped and topic_bumper fields.

There are a few variables you can set, at the top of the file:

• First, be sure to set the name of your phpbb database on the first line.
• @from_date – only bumps performed since this timestamp will be undone. Defaults is 0, so covers all bumps
• @age_when_bumped – only reverts bumps that were done on threads at least this old (when they were bumped). Defaults to one week. The idea is that bumping a new thread is probably perfectly valid, but bumping one from the distant past isn’t.
• @post_margin – Because the timestamp of the last post in the bumped topic has been overwritten, there is no way to set it back to its original, correct time. Instead, we set it to an appropriate time, shortly after the previous post. This variable sets how long after the previous post we would like to set it; default is 10 minutes.

This code assumes the default phpbb table prefix of ‘phpbb_’. If you used something different, you should do a search/replace of all instances of ‘phpbb_’ with ‘[your prefix]_’.

Finally, if you want, you can unbump all topics, rather than just those bumped by anonymous users. Do this by commenting out the first CREATE TEMPORARY TABLE statement, and uncommenting the second, as the inline comments describe.

I hope you like it! I’m more of a MySQL journeyman than an expert, so if you spot anything that could be improved, let me know. And of course, BACK UP YOUR DATABASE before you try this. I take no responsibility for things that go wrong. Use this code at your own risk, and all that.