HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Please upgrade here. These earlier versions are no longer being updated and have security issues.

How to mass remove/delete [Deleted User] from Dashboard > Users > Users ?

edited July 2013 in Vanilla 2.0 - 2.8

I recently moved a vanilla installation (http://pennlpssa.org/forum) to a new server (http://173.236.60.210/~lpssa/forum/). The export/import went smoothly except for one weird issue. When I visit Dashboard > Users > Users it says there are 18,838 users, instead of 39.

On the original installation, I did have that many users, due to rampant spam. After I installed the Botstop plugin and changed Registration to Confirm Email, I deleted 18,000+ spam users with the Cleanser plugin, whittling it down to a legitimate 39. But now my members list has ballooned back to that absurd number, which includes 18,000+ users with the name [Deleted User]. I compared my original vanilla database to the new one, and they both show the same amount of users under GDN_User, which is to say 18,838. But on the original install, it only recognizes and/or displays 39 as legitimate, i.e. only 39 members actually show up under Dashboard > Users > Users, and on the "Members List" section.

Any ideas how to fix this, without deleting 18,000+ database entries? I already tried that and it's ridiculously slow loading even 1000+ entries at once, let alone deleting them. My database seems to time out after 100 at a time.

Thanks!

Andrew

Best Answer

Answers

  • peregrineperegrine MVP
    edited July 2013

    In phpmyadmin there is an option to enter sql commands, for the above command.

    not sure if you were using that option or if you were browse deleting which would be much slower.

    The other option is to export the user table and then delete lines that have [Deleted User] via some perl or sed or awk script or some editor and then import table with the 39 users.

    or you could possibly sort the table with by name with phpmyadmin and export the first 39 members. empty the table and then import the 39

    or if all your deleted applicants are greater than a certain userid with no other valid applicants you could delete

    Delete From Gdn_User where UserID > = some number

    after you do the large delete, perhaps an optimize or repair or whatever it is might also help performance (just a wild logical guess).

    also check out http://vanillaforums.org/addon/registrationrestrictlogger-plugin
    not sure if confirm email has a box for "why do you want to join", but if it does, it might help also, along with botstop.

    you can ftp the log file and import into a spreadsheet based on pipe delimeters and sort and find who the problem spammers might be.

    Also banapplicant, i haven tried because I don't have a live forum, but it looks pretty good too.

    might give you some ideas of who to block via .htacess deny.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • One caveat:
    this is from memory I believe it is userrole table (the table with userid and roleid) since i don't have access to database to look.

    if no of entries of userrole table has 39 entries as well (you said your user table had 39 legitimate users) you should be fine.

    But if you have banned members I can't recall if they are marked as Deleted User or if you have deleted users but left their content in, then removing [Deleted Users] might cause some issues with some deleted users.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

    UnderDog
  • Delete From GDN_User where name = "[Deleted User]" limit 1000;

    This worked like a charm! You can see the correct amount of members here: http://173.236.60.210/~lpssa/forum/index.php?p=/plugin/MembersListEnh

    Thanks so much for the fast and thorough feedback. You are truly invaluable to this Vanilla community.

    Andrew

    peregrine
  • peregrineperegrine MVP
    edited July 2013

    @andrewborstein said:

    Thanks so much for the fast and thorough feedback

    Since you donated for one of my plugins a while back and didn't ask for features and your donation was no strings attached.

    I tried to provide extra help :)

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

    andrewborstein
Sign In or Register to comment.