Problem with board after pruning a lot of spambots

Discussions about the phpBB3 Forum. How to get the best from this powerful script.
Note that there is no official support for phpBB3 here, a place to share more than to ask for.

Moderator: Moderators

Problem with board after pruning a lot of spambots

Postby eoinzy » Mon Dec 24, 2012 10:51 am

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.
eoinzy
 
Posts: 28
Joined: Thu Jan 22, 2009 2:07 am
Location: Perth, Australia

Advertisement

Re: Problem with board after pruning a lot of spambots

Postby Meis2M » Wed Dec 26, 2012 5:31 pm

did you repair your database?
Meis2M
PR1
PR1
 
Posts: 165
Joined: Sat Aug 08, 2009 2:53 pm
Location: www.zohall.com

Re: Problem with board after pruning a lot of spambots

Postby eoinzy » Thu Dec 27, 2012 3:50 am

How do i repair my database?
eoinzy
 
Posts: 28
Joined: Thu Jan 22, 2009 2:07 am
Location: Perth, Australia

Re: Problem with board after pruning a lot of spambots

Postby eoinzy » Wed Jan 02, 2013 8:03 pm

Is repairing the database an option you can do in the admin control panel, or is it done via phpmyadmin or something?
eoinzy
 
Posts: 28
Joined: Thu Jan 22, 2009 2:07 am
Location: Perth, Australia

Re: Problem with board after pruning a lot of spambots

Postby HB » Wed Jan 09, 2013 5:38 pm

eoinzy wrote: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.
Dan Kehn
HB
phpBB SEO Team
phpBB SEO Team
 
Posts: 1509
Joined: Mon Oct 16, 2006 2:25 am

Re: Problem with board after pruning a lot of spambots

Postby eoinzy » Wed Jan 09, 2013 8:55 pm

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.
eoinzy
 
Posts: 28
Joined: Thu Jan 22, 2009 2:07 am
Location: Perth, Australia


Return to phpBB Forum

 


  • Related topics
    Replies
    Views
    Last post

Who is online

Users browsing this forum: No registered users and 20 guests