Spammers!

Any general or UNIT-e specific news
Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
User avatar
robinwilson16
Site Admin
Posts: 185
Joined: Sun Oct 16, 2016 6:46 pm
Forename: Robin
Surname: Wilson
College Name: FEA
Position: MIS Consultant
LinkedIn Profile: https://www.linkedin.com/in/robinwilson16
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Spammers!

Post by robinwilson16 » Wed Jan 24, 2018 11:54 pm

Hello All

Really sorry but I just realised the forum was getting hit with some serious spam.

In total I have had to delete over 70,000 topics mainly about essay writing.

I have also enabled further validation checks on new user registrations and restrictions on how many posts a new user can make.

Due to the volume of posts I was unable to use the admin panel as it kept timing out so I resorted to writing some SQL queries and running them over SSH (even PHPMyAdmin was timing out).

In case it is of interest, I had to first kill SQL queries that were running from my attempts to sort this out from the admin panel as these were placing locks on the tables which resulted in this error every time I tried to run a delete query on the data:

Code: Select all

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

I needed to kill the sessions that were placing the locks on the tables:

Code: Select all

show open tables where in_use > 0;
show processlist;
kill <ID of process displayed above>
I then looked on the forum at the users causing the chaos and banned them, looked in the phpbb_banlist table for the ID of the user and then ran this code to remove all their spam posts:

Code: Select all

DELETE FROM phpbb_posts WHERE poster_id = <ID of spammer here>;
DELETE FROM phpbb_topics WHERE topic_poster = <ID of spammer here>;
I could have used an IN query and done them all at once but due to the volume of data to delete it seemed better to do it one by one.

This deleted all the spam posts but the forum still thought there were thousands of pages so I fixed this by going to Admin CP > Forums and pressing the orange resyncronise button.

Next I had to tidy up the search index and again this timed out from the admin panel so I used this SQL:

Code: Select all

DELETE FROM phpbb_search_wordmatch;
DELETE FROM phpbb_search_wordlist;
I was then able to go to Admin CP > Maintenance > Search Index and create a new index.

Lastly I optimised the tables as they had grown massively in size:

Code: Select all

optimize table phpbb_posts;
optimize table phpbb_topics;
optimize table phpbb_search_wordmatch;
optimize table phpbb_search_wordlist;
This seems to have done the job and hopefully the changes I have made will stop any more spam.

Robin

Post Reply [phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable
[phpBB Debug] PHP Warning: in file [ROOT]/vendor/twig/twig/lib/Twig/Extension/Core.php on line 1266: count(): Parameter must be an array or an object that implements Countable

Who is online

Users browsing this forum: No registered users and 0 guests