Archived Forum Post

Index of archived forum posts

Question:

OSQA SQL to Delete Non-Validated User Accounts

Feb 25 '15 at 10:21

I am sharing this SQL script to help other OSQA forum administrators that may wish to clean out thousands of SPAM-robot-created user accounts. This deletes all non-validated user accounts from the database.

Note: Be sure to review the script prior to running. Also, the SQL commands must be run in-order. Running the commmands one at a time, or a few at a time is good for cautious administrators.

delete from forum_actionrepute where user_id in (select id from auth_user where is_active = false);

delete from forum_award where user_id in (select id from auth_user where is_active = false);

delete from forum_actionrepute where action_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

delete from forum_vote where action_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

update forum_node set last_edited_id = null where last_edited_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

delete from forum_nodestate where action_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

delete from forum_award where action_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));
delete from forum_award where trigger_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

delete from forum_action where user_id in (select id from auth_user where is_active = false);

delete from forum_validationhash where user_id in (select id from auth_user where is_active = false);

delete from forum_nodestate where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false));

delete from forum_award where trigger_id in (select id from forum_action where node_id in
(select id from forum_node where author_id in (select id from auth_user where is_active = false)));

update forum_node set last_edited_id = null where last_edited_id in (select id from forum_action where node_id in
  (select id from forum_node where author_id in (select id from auth_user where is_active = false)));

delete from forum_vote where action_id in (select id from forum_action where node_id in
(select id from forum_node where author_id in (select id from auth_user where is_active = false)));

delete from forum_flag where action_id in (select id from forum_action where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false)));

delete from forum_actionrepute where action_id in (select id from forum_action where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false)));

delete from forum_action where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false));

update forum_noderevision set node_id = null where author_id in (select id from auth_user where is_active = false);
update forum_node set active_revision_id = null where author_id in (select id from auth_user where is_active = false);

delete from forum_noderevision where author_id in (select id from auth_user where is_active = false);

delete from forum_questionsubscription where user_id in (select id from auth_user where is_active = false);
delete from forum_questionsubscription where question_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false));

delete from forum_node_tags where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false));

update forum_node set abs_parent_id = null where abs_parent_id in (select id from forum_node where author_id in 
(select id from auth_user where is_active = false));

update forum_node set parent_id = null where parent_id in (select id from forum_node where author_id in 
(select id from auth_user where is_active = false));

delete from forum_noderevision where node_id in (select id from forum_node where author_id in (select id from auth_user where is_active = false));

delete from forum_node where author_id in (select id from auth_user where is_active = false);

delete from forum_subscriptionsettings where user_id in (select id from auth_user where is_active = false);

delete from forum_userproperty where user_id in (select id from auth_user where is_active = false);

update forum_node set last_activity_by_id = null where last_activity_by_id in (select id from auth_user where is_active = false);

delete from forum_authkeyuserassociation where user_id in (select id from auth_user where is_active = false);

select * from forum_markedtag where user_id in (select id from auth_user where is_active = false);
delete from forum_markedtag where user_id in (select id from auth_user where is_active = false);

delete from forum_actionrepute where user_id in (select id from auth_user where is_active = false);

delete from forum_award where user_id in (select id from auth_user where is_active = false);
delete from forum_actionrepute where action_id in (select id from forum_action where user_id in (select id from auth_user where is_active = false));

delete from forum_action where user_id in (select id from auth_user where is_active = false);

delete from forum_validationhash where user_id in (select id from auth_user where is_active = false);

delete from forum_subscriptionsettings where user_id in (select id from auth_user where is_active = false);
delete from forum_userproperty where user_id in (select id from auth_user where is_active = false);

delete from forum_user where user_ptr_id in (select id from auth_user where is_active = false);

delete from auth_message where user_id in (select id from auth_user where is_active = false);

delete from auth_user where id in (select id from auth_user where is_active = false);