[Mediawiki-l] Deleting users.

Tue May 31 00:09:53 UTC 2005

One thing that could be done is to create a user called "spambot" and
reassign any references necessary to that.

Here's places where the ID should just be deleted (where {UID} is a
bad ID), described in SQL:
* DELETE FROM `user` WHERE `user_id` = {UID};
* DELETE FROM `user_newtalk` WHERE `user_id` = {UID};
* DELETE FROM `user_rights` WHERE `ur_user` = {UID};
* DELETE FROM `watchlist` WHERE `wl_user` = {UID};

Here's places where the ID should be changed (same format as above,
except {NewID} is the ID of "spambot"):
* UPDATE `validate` SET `val_user` = {NewID} WHERE `val_user` ={UID};
* UPDATE `recentchanges` SET `rc_user` = {NewID}, `rc_user_text` =
'SpamBot' WHERE `rc_user` = {UID}
* UPDATE `archive` SET `ar_user` = {NewID} WHERE `ar_user` = {UID}
* UPDATE `old` SET `old_user` = {NewID} WHERE `old_user` = {UID}
* UPDATE `oldimage` SET `oi_user` = {NewID}, `oi_user_text` =
'SpamBot' WHERE `oi_user` = {UID}
* UPDATE `cur` SET `cur_user` = {NewID}, `cur_user_text` = 'SpamBot'
WHERE `cur_user` = {UID}
* UPDATE `image` SET `image_user` = {NewID}, `image_user_text` =
'SpamBot' WHERE `image_user` = {UID}
* UPDATE `logging` SET `log_user` = {NewID} WHERE `log_user` = {UID}
(I skipped ipblocks above. If any of these people are blocking IPs,
they shouldn't be deleted).

Also,  ss_users in site_stats should be adjusted appropriately.

The basic technique is to delete the user and change/remove all
references to them.

Disclaimer: I do not recomend just running these queries on your DB. I
am not known for my SQL abilities, so it is best to validate them or
just user what you need.

On 5/29/05, Myria <myria at wolfandturtle.net> wrote:
> 
> I know the official line is that one should never need to delete a user,
> but, as with a few others here, my wiki recently got 'registration spammed'
> (if you can call it that). There are roughly 200 users with names like
> "0021fb" and "34a5c8" (see
> http://wolfandturtle.net/Indigo/index.php?title=Special:Listusers&limit=500&
> offset=0). Since my site attracts surprisingly few cyborgs from the future
> (they have names like that, dontchaknow...), I assume this was all done by a
> bot. Why a bot would do this, I have no idea, but it almost certainly wasn't
> to further the goals of my wiki. And, since these 200-odd machine annoyances
> represent not quite half my 'real' registered user base and make user
> statistics, the user list, and god knows what else essentially useless or
> annoying to use, and since they bloody well annoy me to no end, I'd like to
> delete them... With extreme prejudice.
> 
> Unfortunately this proves to be something far more easily said than done. I
> can delete the little buggers out of the User table, which excises those
> faux-cyborgs from the user list, but the wiki still shows the same number of
> users, regardless. Poking around the database I see numerous references to
> those user_id's in places like user_rights (Users have rights? Why doesn't
> anyone ever tell me these things...?), and I assume that the number of users
> on the statistics page is either coming from one of those or from, perhaps
> even more simply, the highest user number.
> 
> All of which leads me to my question(s). First, if I delete these users from
> the user table and then troll through the rest of the database deleting out
> all of the places those particular user_id's show up, are those user_id
> blank spaces going to cause me problems later? Second, is there any way to
> renumber the non-bot created users that remain (there having been people who
> signed up during and after the couple of bouts of 'bot user creation) --
> mind you, 98% or so of the legitimate users on my wiki have not actually
> done squat (In fact I can't imagine why they signed up in the first place,
> but what do I know?) so renumbering them, I should imagine, shouldn't cause
> huge issues. Lastly, is there any other option for getting rid of these,
> short of wiping the whole bloody thing as having been an excruciatingly bad
> idea in the first place, that I haven't thought of? I really don't relish
> the thought of going through and manually changing the /nicks of 200-odd
> users to Annoyingbot1, Annoyingbot2, Annoyingbot3... Annoyingbot201. Not to
> mention that it really doesn't solve the problem.
> 
> Any suggestions... Well, any suggestions short of filling the server room
> with C4, as I'm already contemplating that one, would be welcome...
> 
>                                                 Myria
> 
> -------------------------
> IndigoWiki URL     : http://wolfandturtle.net/Indigo/
> MediaWiki Version  : 1.4.3
> PHP Version        : 4.3.10
> MySQL Version      : 4.0.16
> Sanity Version     : .1alpha
> 
> _______________________________________________
> MediaWiki-l mailing list
> MediaWiki-l at Wikimedia.org
> http://mail.wikipedia.org/mailman/listinfo/mediawiki-l
> 

-- 
-------------------------------------------------------------------
http://endeavour.zapto.org/astro73/
Thank you to JosephM for inviting me to Gmail!
Have lots of invites. Gmail now has 2GB.