So I have over 10,000 spam users and need to delete them1. The bulk delete in multisite only does 20 at a time and is somewhat slow so I wanted a better way to delete them all. This could be a massive mistake, if you are reading this because you need the same thing, you absolutely must thoroughly test it to see if it works for you. It’s unforgiving.
Caveats here: it looks like they’re just registering and logging in, so I have a lot of logged in spam users. I’m not sure what to do about that. Pffft.
Ok, so. How does multisite bulk delete users? When I get to the confirmation screen — “What would you like done with content owned by… ” — the action is
allusers eg /wp-admin/network/users.php?action=allusers. Oh I don’t need that. Well what happens when I click the delete button? The action is
dodelete and this is what happens:
First the user is deleted from any blogs they’re on — this bit handles reassigning the content — then they are deleted using
wpmu_delete_user($id). I don’t think that removing a user from a blog is necessary if there’s no content to be reassigned but let me look at that function.
Yeah, that’s about right.
function remove_user_from_blog updates the usermeta and then reassigns any content if necessary. If there’s no reassignment to be done, running
remove_user_from_blog is a bit redundant because
wpmu_delete_user is going to check and run it if necessary again anyway.
Do I need to delete the blogs of the user then delete the user? Well, no. All the usermeta is going to be deleted anyway, so I think I can safely skip that step.
wpmu_delete_user also deletes the links but I don’t use those so I can safely ignore.
BUT I need to know the blogs so that I can delete the posts in the blogs.
Here’s what I want to do after looking at all this:
- Delete all the posts and associated postmeta a user has in whichever blogs they’re a part of
- Delete all the usermeta for the user
- Delete the user
However, I’m in the fortunate situation that I know none of these users have posted, so I can just do 2 and 3. Also, if I don’t delete the posts and mess up and delete someone’s registration who is not a spammer, it will be much easier to fix. I just want to just delete the users and their usermeta.
Is there a proper bulk delete? Ie one that deletes a bunch of users with one query to the database? Hmmm. I don’t think so. Damn.
I could run
wpmu_delete_user 10,000+ times but I think that would be a bit much plus it would delete the posts unless I reassigned them.
There are a couple of ways I could do this:
- A proper all in one mysql query (this would be much easier with foreign key constraints I believe but there aren’t)
- Two step process: delete users then delete all usermeta not associated with a user. This is not elegant but is simple and easy to test.
I’m going with 2 of course, have never been accused of elegance in my life, not about to start now.
Here is my process:
- Back up everything
- Check that I’m deleting the correct email address with no accidents. The one I’m going for is hotmails.com (not hotmail, *hotmails*). I can’t check them all, so I’ll look at 500:
SELECT * FROM `wp_users` WHERE `user_email` LIKE '%@hotmails.co%' LIMIT 500
- Check my statement to delete the usermeta – this should return nothing at the moment:
SELECT * FROM `wp_usermeta` AS um WHERE NOT EXISTS ( SELECT * FROM `wp_users` AS u WHERE um.user_id = u.ID )
- If those both look ok, delete all users with the desired email address:
DELETE FROM `wp_users` WHERE `user_email` LIKE '%@hotmails.co%'
- Then delete from usermeta:
DELETE FROM `wp_usermeta` WHERE NOT EXISTS ( SELECT * FROM `wp_users` AS u WHERE `wp_usermeta`.user_id = u.ID )
Thus far nothing is breaking. It could yet still all go horribly wrong but I don’t seem to have a ton of missing users or anything.
I tested and retested this and am fairly happy with how it turned out although again, I will wait for about a day before calling it a success. It took me a few hours in all, mainly from figuring out that doing a plugin or something like that was not the best idea. I feel like I have a little more control working directly with the database; I am not sure a plugin which can bulk delete users based on part of their email address is a wise idea. 🙂
1I think they used a script with unique registration details and IP address. It seems to run about every 30 seconds. I’m using the Stop Spammers plugin to stop them now.