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

andrewborstein

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

peregrine

I don't think there is any way from the dashboard

short of phpmyadmin with this command. Try limit 1 to see if it is correct first.

is this what you tried before: if not backup database and see if it works better.

Delete From Gdn_User where name = "[Deleted User]" limit 1000;

(double check columns names I can't see my database because I am at another computer).

or try it without limit.

By the way, thanks for your donation a while back,

The delete with cleanser log uses the standard delete as the dashboard delete which replaces applicant user names with [Deleted User].

Both members list enhanced and the dashboard don't show Deleted Users if full content removed (since they are considered deleted.

However, I think it would be beneficial if disapproved applicants where purged from the user table instead of lingering around with "dead records" and bloated databases.

The process could be changed in cleanser plugin, but i didn't want to do something different then the current standard process that vanilla uses to delete users.

peregrine

I don't think there is any way from the dashboard

short of phpmyadmin with this command. Try limit 1 to see if it is correct first.

is this what you tried before: if not backup database and see if it works better.

Delete From Gdn_User where name = "[Deleted User]" limit 1000;

(double check columns names I can't see my database because I am at another computer).

or try it without limit.

By the way, thanks for your donation a while back,

The delete with cleanser log uses the standard delete as the dashboard delete which replaces applicant user names with [Deleted User].

Both members list enhanced and the dashboard don't show Deleted Users if full content removed (since they are considered deleted.

However, I think it would be beneficial if disapproved applicants where purged from the user table instead of lingering around with "dead records" and bloated databases.

The process could be changed in cleanser plugin, but i didn't want to do something different then the current standard process that vanilla uses to delete users.

peregrine

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.

peregrine

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.

andrewborstein

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

@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