Delete spam users

Dear all,

I forgot to put a captcha in subscribing users, so I have a lot of spam users (around 240000). The only way to remove them that I think is by deleting all users that have the role of reader and don’t have other roles (author, reviewer
Then I think the query will be:

DELETE FROM users WHERE user_id NOT IN (SELECT user_id FROM roles where role_id <> 1048576)
DELETE FROM roles WHERE user_id NOT IN (SELECT user_id FROM users)
DELETE FROM user_settings WHERE user_id NOT IN (SELECT user_id FROM users)
DELETE FROM user_interests WHERE user_id NOT IN (SELECT user_id FROM users)
DELETE FROM notifications WHERE user_id NOT IN (SELECT user_id FROM users)
DELETE FROM comments WHERE user_id NOT IN (SELECT user_id FROM users)
Is it correct? I don’t know if is necessary to delete anything else or do it differently.

Thank you very much.

Hi @ipa,

I would not recommend doing this manually in the database, for risk of leaving other entries lying around. You can use the tools/mergeUsers.php script to do it programmatically.

Regards,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

Sorry, but this script is for merge, how can I use this for deleting users with specific role conditions?

thank you!

Hi @ipa,

A merge is equivalent to a delete, except that any “assets” (e.g. submissions, peer reviews, etc) are transferred to another user so the academic record is not deleted.

Regards,
Alec Smecher
Public Knowledge Project Team