Problem with board after pruning a lot of spambots

[eoinzy]

Hi,

My site is http://www.dingostew.com

I had my site "attacked" and got about 15 pages of new registrations by spam bots. I have since updated to the latest version of phpBB from this site.
However, after I pruned the users, the database did not update properly. There are still topics where it says the last user to post was the deleted spam user. When I click on the username, it says the user does not exist anymore.
The board index also says that the last post in some sub-forums were by the deleted users. One sub-forum even says there are no posts in that forum, even though there are lots of posts when you click inside.

Is there a way to resync this data? I tried all the options on the main screen in the Admin control panel, but they don't work. I am hoping I do not have to manually edit the database in notepad or something, so if somebody knows an automatic way to fix this, it would be much appreciated.

As it stands, the database is now over 100mb, which is about 5 times what it should be, so there must be a lot of the spam data still in the database!!

Thanks.


PS, I have found someone with the same issue over on the phpbb official board here: https://www.phpbb.com/community/viewtop ... &t=2167047 .
They won't help me though, because I have the phpbbseo mod installed.

[Meis2M]

did you repair your database?

[eoinzy]

How do i repair my database?

[eoinzy]

Is repairing the database an option you can do in the admin control panel, or is it done via phpmyadmin or something?

[HB]

There are still topics where it says the last user to post was the deleted spam user. When I click on the username, it says the user does not exist anymore.

When you delete a user, you have the choice of deleting all their posts or keeping them. If you used the latter option, the member profile is deleted and the posts associated with that (former) member are assigned to "Guest" (anonymous). Since the name of the (former) member is known, the post's poster name is retained. So, for example, if you delete user "SpamFan" and don't choose the "delete all posts" option, the poster ID is set to anonymous (ID = 1) and the poster name is kept as "SpamFan". It may seem a little odd that you have the option of keeping named anonymous users in your database, but presumably the folks at phpBB thought it was a good idea for cases where you want to ban someone but keep their posts intact.

It sure would be nice if this tool handled removal of named anonymous posts, but it doesn't appear to...

http://www.phpbb.com/support/stk/

I don't have a SQL script to correct the problem, but it's not terribly messy, assuming you don't allow Guest posts on your board. If you don't allow Guest posts, then you should be able to delete all records by Guest (poster_id = 1) in the posts table and then reset the topic_last_poster_name and topic_last_poster_id column in the topics table. In case it's not clear, the topics table has some of this meta-information about the posts table to avoid an expensive join. As a consequence, the two tables can become unsync'd as yours has.

By the way, to avoid any surprises, I recommend testing the above on a LOCAL server, not your live board. Here's how to setup one:

How to set up a local server using xampp

PS: Remember to purge the cache after updating your database, otherwise you'll continue to see "bogus" entries in your forum until they expire.

[eoinzy]

Hi,

Thanks for the detailed reply.

I must have forgotten to click the "delete posts" option. I thought I did but obviously must have forgotten.

I'll have a look in phpmyadmin and see if I can see posts by user 1.

Thanks again.