login about faq

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);

asked Feb 25 '15 at 10:21

chilkat's gravatar image

chilkat ♦♦
11.8k316358420

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×9
×6
×1

Asked: Feb 25 '15 at 10:21

Seen: 929 times

Last updated: Feb 25 '15 at 10:21

powered by OSQA