Porchy Web Development

Bulk deleting users in WordPress multisite

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:

Screen Shot 2017-12-14 at 12.28.24.png

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

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.

Join the newsletter

Subscribe to get the latest content by email.

Powered by ConvertKit. If you give me your email address, you may receive emails from me (JJ) about posts on this site. You can unsubscribe at any time.

Porchy Ltd is a company registered in England, no. 12035925

VAT Registration no. 331196421

Built with WPGraphQL and Gatsby

© 2019 - 2021