There are a spammer(s) out there constantly creating accounts on my site. The accounts are 'blocked' by default so they do not pose a problem, nor does the spammer gain anything from them.
Still, I would like to delete them all but without having to click on the /admin/people page 150 times: canceling accounts 50 at a time.
Is there a way to cancel all 'blocked' accounts in one step?
Drush only appears to let me cancel one at a time...
Use the Views Bulk Operations module.
This module augments Views by allowing bulk operations to be executed on the displayed rows. It does so by showing a checkbox in front of each node, and adding a select box containing operations that can be applied. Drupal Core or Rules actions can be used.
You can create a view that shows only the blocked accounts and adds bulk operations to it. In this way, once you select all the accounts with a single click, you can delete them by selecting the operation to do and clicking on the submission button.
Deleting users directly from database is not a good idea. either done through drush or by interacting directly with the database through SQL DELETE. Some modules, for instance, create robot users and these tend to be blocked. Just deleting them may interfere with the module's operation and even make the module fail, leaving you with orphaned data and inexplicable error messages.
When spam registrations appear as "blocked" users, they're blocked because their accounts remain unverified (spammers, as a rule, never verify, so their accounts never progress from the blocked status).
A project named LoginToboggan gives you the option to have unvalidated users purged from the system at a pre-defined interval. This will also take care of any spammers that register in the future, without making it necessary to introduce protective measures such as CAPTCHA, that tend to discourage legitimate users from signing up.
Using LoginToboggan to bulk-remove spam users is much safer and more robust than hacking the database yourself.
If you don't want to install a module to do this, as an alternative, you can delete the unverified users using drush and user_delete().
This is what I did, without adding in any extra modules or coming up with any fancy scripts or bash commands. Obviously, I'm going to get alot of hate for this as it involves hacking core temporarily.
But this is the FASTEST way. Period.
No worries about missing any drupal hooks. Obviously if you set the limit number to high, it might crash your sql server. So set it appropiately. I had 10k worth of blocked users to delete. I set it to 5000 and just had to delete users only on 2 pages.
For Drupal 8 with drush under Linux (with bash) you can use following solution:
echo 'select name from users_field_data where status=0 and uid!=0' | mysql --silent drupaldatabase > userlist
for username in `cat userlist`; do drush user-cancel $username -y; done
This is both rather slow (1 user/s) and safe. It doesn't work with special UTF-8 character names and names with spaces.
echo 'select name from users_field_data where status=0 and uid!=0' | psql service=pg_drupal -t > userlist if you use PostgreSQL.
Or use this module advance user module and token module and open people menu in drupal admin and see the advance tab menu and delete bunch of user records.
https://www.drupal.org/project/advuser
and
For Drupal 8, there is the user_prune module that have few options such us delete never logged in users, or blocked users.
https://www.drupal.org/project/user_prune It deletes upto 500 users that fit certain criteria per cron run.
If you have huge number and want do it fast, then backup your db and do it via mysql query (not recommended but it works). The below query is assuming you using prefix for your db tables. As of this writing the 3 tables where users are located are:
[yourprefix_users , yourprefix_users_data, yourprefix_users_field_data ]
delete u,ud,ufd from yourPrefix_users u inner join
yourPrefix_users_data ud ON u.uid = ud.uid inner join
yourPrefix_users_field_data ufd ON ufd.uid = ud.uid
where access=0;
That deletes those that never logged in (access=0) you can use status=0 for blocked ones.
If you are sure that all of the blocked accounts need removing, then you could run a query in phpmyadmin (or your mysql admin of choice):
DELETE FROM users WHERE `status` = 0 AND `uid` != 0;
That should get rid of all blocked accounts and leave the anonymous user. Of course you would DEFINITELY want to back your DB up first. Also, realize this also get rid of any accounts that may be legitimate, but that you may have set status as 'blocked' for some reason.
Note: I should say that I know way more about 6 than 7. So if the users table is drastically different in Drupal 7, this may be incorrect.
This is my late to the party solution how to use get this done without having to add vbo. The command itself is probably a bit fragile and may break under certain configurations of mysql but the deletion itself should be safe.
drush sqlq "SELECT uid FROM users WHERE uid >= 629" | grep -v uid|tr '\n' ','|awk '{print "drush ev \"user_delete_multiple(array("$1"))\""}'|sh
for nodes you can alter appropriately
drush sqlq "SELECT nid FROM node WHERE nid >= 435193 AND type='school'" | grep -v nid|tr '\n' ','|awk '{print "drush ev \"node_delete_multiple(array("$1"))\""}'|sh
Notice, you need to change nid/uid in the grep portion along with the function call to appropriately grab the uid/nid values.
IN drupal 8 you must delete content for 3 tables.
users_field_data WHERE uid >= 240users_data
WHERE uid >= 240 DELETE FROM users WHERE uid >= 240