So I have over 10,000 spam users and need to delete them¹. 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:

1. Delete all the posts and associated postmeta a user has in whichever blogs they’re a part of
2. Delete all the usermeta for the user
3. 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:

1. A proper all in one mysql query (this would be much easier with foreign key constraints I believe but there aren’t)
2. 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:

1. Back up everything
2. 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
   

3. 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 )

4. If those both look ok, delete all users with the desired email address:

   DELETE FROM `wp_users` 
   WHERE `user_email` 
   LIKE '%@hotmails.co%'

5. 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.  🙂

¹I 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.