2

I have a site with 70K spam subscribers, and I don't need any of them. So I need to delete all subscribers from wp_users and each user's associated meta from wp_usermeta using a query in adminer and/or phpmyadmin.

But to keep from crashing the server, so how can I delete a few thousand at a time?

This seems to be the basic query I need:

DELETE
FROM  wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE meta_key = 'wp_capabilities'
AND meta_value  LIKE '%subscriber%
//except how do I select a few thousand at a time?

But how do I select a few thousand at a time?

  • 2
    Perhaps the SQL LIMIT clause? DELETE ... FROM ... INNER JOIN... ON... LIMIT 3000 – josh Nov 2 '14 at 4:00
  • Before you do this, make sure you have a backup! – SLH Nov 2 '14 at 12:34
  • 1
    True; don't delete your admin account :) – josh Nov 2 '14 at 22:21
  • mysql> delete wp_users, wp_usermeta FROM wp_users INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id WHERE meta_key = 'wp_capabilities' AND meta_value LIKE '%subscriber%'; Query OK, 475666 rows affected (9,26 sec) it's very fast with thousands of subscribers, anyway :) – strangeman Feb 10 '16 at 7:10
  • @strangeman: not necessarily; all depends on the server, of which I had no control over, nearly two years ago. – markratledge Feb 10 '16 at 17:41
4

My favorite way to do something like this is not via database hacking (which I am always concerned will have side effects), but via a command line tool.

For example, giving a functioning WP CLI install, you can simply do this:

wp user delete $(wp user list --role=subscriber --field=ID --number=10) --reassign=1

The reassign parameter is the user ID of the user to reassign content to. We can use "--number" (the limit), even though it is not well documented, because WP_User_Query supports it.

There you have it - a clean, one line command line utility to do operations like this.

I'd recommend in this case you try the subquery first "wp user list.." to see which users it will delete. After running the one-liner, you will see information like this:

... Success: Removed user 123 from https://example.com/. Success: Removed user 124 from https://example.com/. ...

3

I had a similar problem and after reading this question I made this plugin (adapted for your question).

Once installed it makes an admin menu under Users called Bulk User Delete when you go to this page, it deletes the users in batches, 20 at a time as per your sql query (my original query was slightly different).

You can make the batches smaller or bigger but this allows you to delete many users without server timeout, it may take a few minutes for lots of spam sign ups.

It uses jquery to reload the page after each batch is processed (this could be done with ajax I guess but I need something quickly).

Maybe someone else will find this helpful, you'll most likely need to adapt the sql query to your situation.

    <?php 
    /*
    Plugin Name: Bulk Delete Spam Users
    Description: Delete the spam sign ups
    Version: 0.1
    Author: Steven
    Text Domain: bulk-delete-spam-users
    */

    add_action('admin_menu', 'my_users_menu');
    function my_users_menu() {
        add_users_page('Bulk User Delete', 'Bulk User Delete', 'read', 'bulk-user-delete', 'active_users_nodel');
    }

    function active_users_nodel() {
        global $wpdb;
        $result = array();
        $result = $wpdb->get_results('
            SELECT wp_users.id, wp_users.user_login FROM wp_users 
            INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
            WHERE meta_key = "wp_capabilities"
            AND meta_value  LIKE "%subscriber%"
        ');

        $users = $result;
        $i = 0;
        echo '<div class="bud-box">';

        $users_remain = $users ? 'true' : 'false';

        $current_user = wp_get_current_user();
        echo '<strong>Orphaned content will be reassigned to:</strong><br />';
        echo 'Username: ' . $current_user->user_login . '<br />';
        echo 'User ID: ' . $current_user->ID . '<br />';

        <h3>currently deleting...</h3>

        foreach($users as $user) {
            $i++;
            if($i < 20) {
                wp_delete_user( $user->ID, $current_user->ID );
                var_dump($user->user_login);
            }
        }
        echo '</div>';
        ?>

        <script>
            (function($){
                $(document).ready(function(){
                    var uremain = <?php echo $users_remain; ?>;
                    if( uremain == true){
                        location.reload(); 
                    }
                });
            })(jQuery);
        </script>

    <?php } ?>
2

You can use Wp function for this. wp_delete_user(); this function work with user id. you can get all subscribers user id with this way: get_users() getting your filtered users.

$get_subscribers = get_users('role=subscriber');
foreach($get_subscribers as $user){
    wp_delete_user($user->ID);
}
  • probably need to extend the php time limit for it to actually deleting all the users and not stopping after 30 seconds – Mark Kaplun Nov 3 '14 at 5:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.