Register Members List Search Today's Posts Mark Forums Read

Reply
 
Thread Tools
  #1  
Old 16 Feb 2016, 09:10
hugh_ hugh_ is offline
 
Join Date: Mar 2005
How I dealt with having to delete 1.3 million SPAM registrations

I recently had the experience of cleaning up a forum which was left running vbulletin 3.6.9 for 8 years. With 1.3 million SPAM user account registrations to it's name. I thought it might be worth passing on some how I went about cleaning the place up.

Summarizing a lot of trial and error which isn't worth repeating. The first thing I did was backup the filesystem and database, then took the copies offline. I upgraded to 4.2.3, which took a while but went without a hitch thankfully. Then I started looking into the 'User Moving/Pruning System' which is limited unfortunately, not least in it's search abilities but mostly to 1,000 deletions at a time. I also looked into whether it could be done using SQL queries but apparently not, shame. And 'List Users Awaiting Moderation' might be able to list many thousands of users but it can't do anything helpful with them. Then I found a handy plugin for automating user deletions and usergroup changes which got my hopes up.

However this too is limited to a 1,000 accounts so I set about deleting users manually until it occurred to me to move all the problem accounts to a single usergroup, move a subset at a regular interval using a scheduled SQL query and delete them using the plugin run as a scheduled task. Unfortunately again here here was another limitation in that run as a scheduled task the plugin was unable to delete more than 100 users at a time on my system at least, but still this was it finally a way of deleting 14400 users (or 100 * 6 * 24) per day automatically.

Some other ideas I picked up along the way. If Who's Online is flooded with SPAM users. You might want to make their usergroup invisible. I also blocked all member profiles from all search engines using robots.txt and applied a mod to limit access to certain usergroup's profile pages. Going forward of course the upgraded forum is now using re:captcha2 and has all the relevant user permissions shored up as much as possible.

Anyway here's some of what I picked up along the way. As always backup thoroughly before attempting any of it!

- SQL to make all users in usergroup X invisible:

UPDATE user SET options=options + 512 WHERE NOT(options & 512) AND usergroupid = X

- SQL to move users selectively from one usergroup to another, with the added limitation of only moving users who's name starts with the letters A through F, have more than 1 post and limit the move to a 1,000 user records only.

UPDATE user set usergroupid = X where (usergroupid = Y AND username REGEXP '^[A-Z].*$' AND posts >=1) LIMIT 1000

- User cleanup mod:

http://www.vbulletin.org/forum/showt...hlight=cleanup

- Hide profiles mod:

http://www.vbulletin.org/forum/showp...4&postcount=23

- How to schedule mysql queries:

http://www.mysqltutorial.org/mysql-t...heduled-event/

My schedule:

CREATE EVENT move_users_for_deletion
ON SCHEDULE EVERY 10 MINUTE
STARTS '2016-02-16 06:59:00' ON COMPLETION PRESERVE ENABLE
DO
UPDATE user set usergroupid = X where usergroupid = Y LIMIT 100

And the schedule I'm running for User Cleanup: 10, 20, 30, 40, 50, 00

Last edited by hugh_; 18 Feb 2016 at 09:57.
Reply With Quote
  #2  
Old 16 Feb 2016, 11:35
SaN-DeeP's Avatar
SaN-DeeP SaN-DeeP is offline
 
Join Date: Jun 2002
Thanks I have 200K + banned/spammers/ etc. users for deletion as well,
Was looking something, will update again if I need any suggestions.

Regards,
Reply With Quote
  #3  
Old 16 Feb 2016, 20:14
hugh_ hugh_ is offline
 
Join Date: Mar 2005
I forgot to say. If you prefer to do things manually. The most efficient way I found is to just keep repeating:

1. Move 995 users to an empty usergroup with a query.
2. Do a Run Now of User Cleanup from the scheduled tasks menu.
Reply With Quote
  #4  
Old 29 Jun 2016, 22:01
hugh_ hugh_ is offline
 
Join Date: Mar 2005
After moving this site to a host with cron access I cleaned up a remaining 165k spam registrations with a scheduled task run one minute before and the following job. 175 users was the most the server could delete at one time but your millage may vary.

9,19,29,39,49,59 * * * * /usr/bin/mysql -u root -p<MYSQLPASSWORD> -e "USE <DBNAME>;UPDATE user set usergroupid = <DELETIONGROUP> where usergroupid = <SPAMGROUP> LIMIT 175" > /dev/null 2>&1
Reply With Quote
Reply



Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


New To Site? Need Help?

All times are GMT. The time now is 00:40.

Layout Options | Width: Wide Color: