Deleting spammers hangs up my board (and the server).

Post by **KevC** » Mon Sep 10, 2012 7:33 pm

DionDesigns wrote:Is flood control enabled by default in phpBB these days? If not, it should be seriously considered.

Yes. The default is 15 seconds.

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 2:03 pm

OK, I've deleted about 400,000 posts (and their offending users) in one day through various attempts of MCP, "Manage Users" & "Prune Users" but there's still the question of Topics started by these Super Spammers. I'm I correct in assuming the only way to delete these topics is by going through the MCP and "Mark All" and "Delete"?

I assumed that when I went
ACP
Manage Users
Basic Tools - Delete All Posts (SUBMIT)
Delete User - Delete Posts (SUBMIT)

That would include all of the topics that they started too. However, that's not the case.

Just wondering if there was something else I was missing?

Thanks.

Post by **KevC** » Tue Sep 11, 2012 2:23 pm

Yes you're right.

Again I think removing the topics too is dependent on the time frame. I've definitely been able to remove topics as well as posts before without any problem.

You could try resyncing though. manage forums > click the resync icon next to a couple to try it. There are also some resync options under the stats box in the admin panel.

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 2:28 pm

It's weird, the topics show up under the forum, but when you actually click on it, it gives you the following error

The requested topic does not exist.

If you look at the forum in question, it says there's 17,000 topics and 17,013 posts. Meaning to me there are 13 good posts there presumably. It's not really worth the time to me to search for those 13 posts, but if they could be saved that's great. It's like the topics are "Shadow Topics" but they're really not. If you'd like to take a look, let me know.

Post by **KevC** » Tue Sep 11, 2012 2:35 pm

Yes I've seen that effect before.
Clicking on the moderator control panel link while looking at the forum itself will allow you to delete whole pages at once very quickly.

Set the page length to 1000 topics and then you only have to do it 17 times.

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 3:13 pm

OK, it appears that most of the spam is gone as well as the Spammers, but the Topic Counter and the DB size didn't decrease all that much.

At the start of this, the topics/posts were relatively close in number over 600,000 each. Now, the Posts Counter reads 2892 yet the Topics Counter says 20179. If you look at the board there isn't that many Topics. I tried the "Resynchronise or reset statistics" options, but the "Topics" didn't change.

Also, at the start my DB size was something like 6.12 GiB, now it's 5.92 GiB. Is there something else that I need to do to get that size reduced?

Post by **KevC** » Tue Sep 11, 2012 4:01 pm

Not sure about the topic counter but part of the database size will be coming from the search tables as they'll now be full of the spam words.

If you drop the contents of the search tables you would then need to rebuild them in the system tab for people to be able to search for old posts again.

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 4:08 pm

Going through

ACP
Maintenance
Search Index

Gives be a blank page, any suggestions?

Post by **KevC** » Tue Sep 11, 2012 4:14 pm

White pages are usually due to suppressed php errors. Check the server logs for an idea on that. It could be a dodgy MOD edit or incomplete board update or something else.

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 9:00 pm

OK, I cleared out my "phpbb_search_wordmatch" tables and that was like 1.9 GiB, but my "phpbb_posts" table has 4.2 GiB in it with only 2900 posts! That can't be right can it? These are good posts, but apparently there's still something hanging up in the DB there.

Any ideas?

Post by **KevC** » Tue Sep 11, 2012 9:06 pm

The posts table can be any size, depending on how much people write.
Are you sure it's Gb and not Mb?

ge_rpger · Post by **ge_rpger** » Tue Sep 11, 2012 9:56 pm

Table = phpbb_posts
Rows = 2886
Size = 4.2 GiB

As I look at my site, I have a "Recent Posts" mod on the top of the forums showing the latest posts, and they're all by someone that has been deleted, if you click on the "Last Post" icon, you get taken to the post, but said poster's post is not there.

Post by **KevC** » Tue Sep 11, 2012 10:07 pm

4Gb doesn't sound right. I've got a 300,000 post database that's only 400Mb.
I suspect the database is just flipping out a bit from all that deleting. You could try running the optimizer on your hosts MySQL manager.

I can't help with the MOD. You'd have to ask the author about that.

ge_rpger · Post by **ge_rpger** » Thu Sep 13, 2012 3:06 pm

OK, I went through my posts in phpMyAdmin and manually (with the help of Excel) added up their data size and came up with 4.2MiB, NOT 4.2GiB!!! So, there's something somewhere adding a bunch of zeros but I can't find it. Holy crap Batman!!!

Anyway, I'm sitting here trying to figure this massive thing out, talking to the ToS rep at my host, telling him all the things "I've" done trying to fix this problem, and he says, "Well, I've never seen the report from phpMyAdmin report something incorrectly". I told him I went through and added them all up and he said, "manually?" I replied, "Well, with the help of Excel, but I did have to Copy and Paste everything!".

So, I mentioned the "Optimizing MySQL", and he clicked it on the individual table "phpbb_posts" and viola! It went from 4.2GiB down to 4.4MiB!!! He comes back with something along the lines of the space was being allocated because of all of the spam, and it generally resets itself after a while.

Anyway, long story short! Thanks for the help! You're awesome Kevin!

ge_rpger · Post by **ge_rpger** » Thu Sep 13, 2012 3:06 pm

So, now off to update my php and add all of these Spam Countermeasures!