Delete Unused Users in MediaWiki
I run the Mac OS X Server FAQ wiki, which runs on MediaWiki version 1.5.8 (the same software which runs Wikipedia). I was doing some routine maintenance tonight and noticed there were approximately 3400 users. When I went to the user list I noticed that the majority of them had names like f23da34. Hmm. Looks like robot junk metal spammers to me.
So, I jumped into the database and ran the following queries to delete any user that had never done anything on the web site except register:
NOTE: This script was used in MediaWiki version 1.5.8. No attempt has ever been made to test this script on other version. Use at your own risk. If you hose your MediaWiki installation, sorry, can’t help you.
ALTER TABLE user ADD COLUMN user_in_use tinyint NOT NULL DEFAULT 0; UPDATE user AS U LEFT JOIN archive AS A ON U.user_id = A.ar_user SET U.user_in_use = 1 WHERE A.ar_user IS NOT NULL; UPDATE user AS U LEFT JOIN image AS A ON U.user_id = A.img_user SET U.user_in_use = 1 WHERE A.img_user IS NOT NULL; UPDATE user AS U LEFT JOIN ipblocks AS A ON U.user_id = A.ipb_user SET U.user_in_use = 1 WHERE A.ipb_user IS NOT NULL; UPDATE user AS U LEFT JOIN logging AS A ON U.user_id = A.log_user SET U.user_in_use = 1 WHERE A.log_user IS NOT NULL; UPDATE user AS U LEFT JOIN oldimage AS A ON U.user_id = A.oi_user SET U.user_in_use = 1 WHERE A.oi_user IS NOT NULL; UPDATE user AS U LEFT JOIN recentchanges AS A ON U.user_id = A.rc_user SET U.user_in_use = 1 WHERE A.rc_user IS NOT NULL; UPDATE user AS U LEFT JOIN revision AS A ON U.user_id = A.rev_user SET U.user_in_use = 1 WHERE A.rev_user IS NOT NULL; UPDATE user AS U LEFT JOIN text AS A ON U.user_id = A.old_user SET U.user_in_use = 1 WHERE A.old_user IS NOT NULL; UPDATE user AS U LEFT JOIN watchlist AS A ON U.user_id = A.wl_user SET U.user_in_use = 1 WHERE A.wl_user IS NOT NULL; DELETE FROM user WHERE user_in_use = 0; ALTER TABLE user DROP COLUMN user_in_use;
Amazingly, there now only about 81 users. Whew. Much better.
