How to restore or re number User ID after mass deletion

vrijvlinder · April 2014

Hi I deleted a bunch of unwanted users from a test forum and I am curious to know how to go about changing the ID of the ones I want to keep.

Example:

I deleted about 400 users. A while back and added two users . One is user 91 the other is user 430.

Is there a way to change their ID via the data base and how.

I could simply remove them and re add them but that would give them other numbers again.

I used cleanser to purge. So I only have 3 users including myself. I want to change their ID to 2 and 3.

The database from what I can tell displays 430 entires approx. But I purged the users.

Thanks for the help.

peregrine · April 2014

when you delete users via vanilla it keeps a [Deleted User] in the user table. (since cleanser uses the standard delete built into vanilla, it also keeps a [Deleted User] in the user table.

the user table also auto-increments I believe even if you delete the [Deleted Users] and you would have to temporarily turn off the auto-incrementing temporarily.

To change the id of a user you would have to match every userid and insertuserid and anything related to a user type of id in all tables so conversations, discussions, userroles etc are matched.

If the user 430 and and 91 don't' have discussions comments or conversations or activity.

just change the User fields in User for for id 2 to match 91 and 3 to match 430.

you could also backup database as you would do before messing with anything.

separately back up the usertable and the userrole table remove all the unnecessary entries, leave your three users in the dumped sql tables.

drop the user and userrole table and restore with your modified user and user role table.

and then your renumbering should start at 4 again.

Linc · April 2014

If there is much content on this forum, it's going to be a gigantic pain.

Note you can reset the auto-incrementer afterward without dropping the tables:

ALTER TABLE GDN_User AUTO_INCREMENT=4;

peregrine · April 2014

probably better to drop anyway don't you think. table are optimized better when reloaded from sql table if you make lots of deletions to table. what say you?

vrijvlinder · April 2014

Yes, keeping[ deleted user ] in the database is problematic. Why keep it if the content is also gone. Does it make more sense to make that UserID available again after deleting a user and his content? Of course this can't work unless the data base can be re sorted somehow...changing the term deleted user to anonymous would be nice if one can't get rid off the [deleted user] part. The reason why it is problematic, if you have a plugin where you select a random user from 1-5 and your users 3-4 have been deleted, then this renders deleted user as a value. And it looks bad to have a deleted user poster.

There is no content by the users I want to keep . Makes that job easier.

I will try the change in the auto increment and report back. Thanks

R_J · April 2014

I guess that couldn't be done without working through all the tables, looking at the columns and their purpose and do a lot of manual UPDATE SQLs. You can start with that SQL to get an idea on where to look at: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%UserID%'.

You can build a list of “free” user ids below a special number and then you could match that free numbers to the used, too high user ids. Afterwards you can build up a script that, given from the info of the SQL above, does a UPDATE TABLE_NAME SET COLUMN_NAME = NewUserID WHERE COLUMN_NAME = OldUserID.

But you can never be sure that some plugin (or even Vanilla itself) uses UserID information that is stored in a column that is not called %userid%. Or maybe there is a column where UserIDs are stored as a serialized array (one of my first plugins did exactly that). For such cases, renumbering UserIDs would be hell.

By the way: I would avoid something like that anyway. If you do not delete some info of a user, another user is related to that piece of information if you give him a formerly used UserID.
And for the user only the username should be relevant, not the number.
If you want to get a random user, get all possible UserIDs and user array_rand.

vrijvlinder · April 2014

If you want to get a random user, get all possible UserIDs and user array_rand.

I have been improving the magpiefeeds plugin. I can add the random array which is a bit of code peregrine gave me he has been collaborating with me much to his dismay I am sure... but the point here is that using a random number of user ID gives you

[Deleted User] as the author unless you specifically set a user id to be the author.

If you have 400 deleted users, you can see the problem of fetching random User ID

So I wanted to know if there was a way I could use smaller number by getting rid of all the [Deleted Users] which I am not sure how I got them in the first place I think a spam attack . For the purpose of making one of the two I have left a default author/UserID or to pick from 1-3

But I can't do that If I wanted to due to the fact that 2 and 3 are deleted users.

If I changed how the User is fetched, instead of ID the name as in this example below
and pick a name then the User ID is irrelevant to pick because it get's attached to the name in the end of this example. So it becomes less random maybe...

$NewUserIDs = array();
         $NewUsers = explode(',', $this->Form->GetFormValue('AddUser', ''));
         $UserModel = Gdn::Factory("UserModel");
         foreach ($NewUser as $Name) {
            if (trim($Name) != '') {
               $User = $UserModel->GetByUsername(trim($Name));
               if (is_object($User))
                  $NewUserIDs[] = $User->UserID;
            }
         }


         $Sender->DiscussionModel->AddUserID($this->Discussion->DiscussionID, $NewUserIDs);

R_J · April 2014

Haven't tested it but that should give you 5 random, undeleted users:

   $NonDeletedUsers = Gdn::SQL()->GetWhere('User', array('Deleted' => 0))->ResultArray();
   $NonDeletedUserIDs = ConsolidateArrayValuesByKey($NonDeletedUsers, 'UserID');
   $RandomUserIDs = array_rand($NonDeletedUserIDs, 5);

vrijvlinder · April 2014

Great idea RJ !! I had no idea I could pick undeleted users to not be chosen... I shall try it and report back. It is 3 am here so it would be best when I am more awake. Although sometimes I have a breakthrough when sleep deprived..

R_J · April 2014

Being tired feels similar to being drunken and coding under alcohol might lead to creative results but reaching your goal could look like trying to reach the other side of the street when you're totally hammered: it is by now means a straight line and it is unsure if you ever will end up where you intentionally wanted to...

x00 · April 2014

A UserID is kind of like a passport number, if you changed the whole person identity has changed.

vrijvlinder · April 2014

yes I understand that, thanks. I just wanted to zero out the database . The best approach I believe is to install it on a new database .

I still don't understand why it is necessary to keep Deleted User IDs if their content has been erased.

Or why keep the content of a deleted user? would it not be best to just ban the user and keep the content ?

peregrine · April 2014

@vrijvlinder said:
yes I understand that, thanks. I just wanted to zero out the database . The best approach I believe is to install it on a new database .

I still don't understand why it is necessary to keep Deleted User IDs if their content has been erased.

Or why keep the content of a deleted user? would it not be best to just ban the user and keep the content ?

suppose the user is not banned - just deleted - so as not to besmirch their name.

nothing stopping you from banning. or adding to this issue

https://github.com/vanillaforums/Garden/issues/1639

at least the current issue is in 2.3 milestones

the kiss principle - keep it simple, stupid.

I am not pointing the kiss principle at anyone, just like the acronym

peregrine · April 2014

V,

one reason I can think of to keep Deleted User or some other place holder for members who were approved (that is no applicants) is so you know who your first 100 members (or whatever number) who joined your forum in case you use badge system that gives to the first 100 joiners. Yes you could ignore deleted users in the first 100 users, if it was programmed that way. It also may keep your forum from erroring out if you totally delete content and userid from user table if a plugin still uses the userid for some reason and that has not been zeroed out when delete user content.

I do believe, however, non-approved applicants should not be kept in the user table as indicated by issue filed above.

vrijvlinder · April 2014

plugins that get from the sql just look for table row column not specific user ID right?

there should be a way to be able to reuse User ID. other wise like you pointed out at github , you just end up with user 3599927366 when there are only 20 users.

each user is distinguished by their unique name and unique email. so it is not as if the user ID would affect that part if it were to be changed or zeroed .

There is a way of keeping wanted content , by never deleting that user as in the case with banning. But deleted users and their content should make the user ID be free again and given to the next user.

peregrine · April 2014

plugins that get from the sql just look for table row column not specific user ID right?

not necessarily. your revised magpie did just that until the r_j transformation. Other plugins may as well.

each user is distinguished by their unique name and unique email. so it is not as if the user ID would affect that part if it were to be changed or zeroed .

there is an option to use non-unique e-mail addresses.

There is a way of keeping wanted content , by never deleting that user as in the case with banning. But deleted users and their content should make the user ID be free again and given to the next user.

my example above. first 100 users to join forum.

personally I think it better not to re-use ID and not to keep [Deleted User] for deleted applicants.
for the reasons I pointed out above. I think Vanilla is optimized to pull info out by userid not name or e-mail. Check out tables and see what is indexed and keyed.

I have no opinion about any thing else. points made. let others continue on in this discussion, I have no more input valuable or useless, whatever the case.

not sure why the need for contiguous ID numbers is that important. (re-use of old ids).

vrijvlinder · April 2014

Ok, I thank you for your insight. does the data base get saturated from these deleted users? Is there a threshold for containing them?

Is this a casualty of forum life in general ? I know in other type forums they never delete users they just call them anonymous. Not sure how that works.

It is a bit disturbing to know there are so many deleted users occupying space. Who had no content.

R_J · April 2014

Theoretically, I don't see any problem in ending up with 20 active users and one of them having the UserID 1234567. The UserID is an internal index the software needs, but nothing to show off to the user.
Mentioning @40152 (which by the way reminds me of "49152" and that triggers something in my brain concerning the days of the good old C64, but I cannot recall something specific...) will not mentioning you, vrijvlinder and I bet that nearly nobody knows his UserID by heart. To my opinion, profile links should link to /profile/userid/username but should be rewritten to /profile/username just to look nicer and somehow "hide" that unnecessary information from the user.

Concerning how it is done in other software: you'll have to have a unique identifier if you database uses more than one table and a number is the best thing for that: it is more memory efficient than a string. But a number isn't very personal and that's why users are able to choose a name. The number is only the internal reference for the db. If you would like to give out nice names and numbers (for whatever reason, employee ids and such maybe) it would be the best to make that additional number an additional column and not touch the internal reference number.

But I've started with "Theoretically..." so here is why: if you have only 20 active users but UserIDs > 100 (and that has nothing to do with the attraction of your forum content), you should better keep bots and spammers from registering at your site. But if it happened, your user table is cluttered. So I would think of just "moving" non active users to a new table. If you want to keep them somewhere or delete them right away is a matter of personal taste.

hgtonight · April 2014

You should always use the user ID to keep track of users. It will always be unique.

The best default auto-increment to use after an insert is the column's max value + 1. Assuming the user ID is a 32 bit unsigned integer, you have nearly 4.3 billion user IDs at your disposal. Worrying about them seems nonsensical to me.

Now, as far as why this happens in Vanilla, applicants reserve a user id at the moment of application. This is required to keep track of all the disparate records associated with the user. When you decline an applicant, it removes all the data, but retains the record in the user table. Why is this? Why don't they remove the record and set the auto-increment on that column to the deleted user's id? Because different storage engines treat auto-increment columns differently. If your storage engine doesn't support grabbing the next available ID (starting at the auto-increment value), what should happen? Generally it just takes the max value of the column and adds 1 to it.

To answer the original question. To get the user id mapping for all other of changes needed:

Backup your existing database
Delete all the deleted user records
Add a new UserID2 column that is set to auto increment
Copy the two fields so you have a mapping for every other table in the DB
Drop the UserID column
Alter the UserID2 column to reflect the UserID original settings (primary, auto increment, not null)

At this point you should have sequential user ids in your user table. You need to figure out if any of the IDs are going to overlap at any point during the transfer. If any of the new User IDs exist in the Old user id list, you will have to map those pairs to another temporary ID to prevent data mismatching. For example

User        Old   New
hgtonight:   23 :  4
peregrine: 1336 : 23
r_j:          4 : 89

You need to either remap these users in a specific order (R_J, hgtonight, peregrine) or you need to use an intermediary ID and not worry about the order.

Once you have this figured out, you need to map any IDs that switched in every other table that uses a user id!

Inspect each table for a user id column
For each column you find, update the table with each of your mapping values. E.g.:
- UPDATE GDN_UserDiscussion SET UserID = 89 WHERE UserID = 4; // Update R_J's user discussion records
- UPDATE GDN_UserDiscussion SET UserID = 4 WHERE UserID = 23; // Update hgtonight's user discussion records
- UPDATE GDN_UserDiscussion SET UserID = 23 WHERE UserID = 1336; // Update peregrine's user discussion records
Repeat for every column in every table that uses user ids

As you can see, this is a very labor intensive process for something that is pure vanity.

vrijvlinder · April 2014

Thanks you guys, this helps me to understand how this works. I understand the hassle involved. It seems more economic time wise and hassle, to just erase and reinstall a fresh install. For that particular forum has not much content by anyone else.

I am not really concerned with how it looks( vanity) , mostly the issues you can run into when dealing with random selections of ID. Of course now that RJ has shown me how to makes sure deleted user is not picked in the random pool that becomes a lesser problem.

My question about saturation still remains. Is there a threshold of deleted users ? Do ID just increment for eternity ?

A problem experience in yet another install, was that I had a problem uploading the files to that install and it partially uploaded rendering my forum useless. Then to make matters worse I called my host and they made an admin account before I had the chance to and the ID 1 is no longer available because I erased their account .

I suppose it does not matter what ID the admin has as long as it has permissions. But it is no longer available as a default admin ID.

I had to reinstall that one from scratch. And did lose everything that was posted. It was not a huge popular forum and the person I built it for does not participate hugely but it was troublesome nonetheless.

Might there be a way to keep ID 1-4 from being given out to anyone unless you allow it ? Or any ID you want to keep. Like the way System Bot has ID 3 as default.

hgtonight · April 2014

@vrijvlinder said:
My question about saturation still remains. Is there a threshold of deleted users ? Do ID just increment for eternity ?

Virtually, yes. There should be almost 4.3 billion available ID numbers. Facebook has what, 1.6 billion users? They might have to worry about running out. Virtually no one else does.

If you do reach the maximum value for a column with auto-increment and try to insert a new row, MySQL will return a failure and you won't be able to add new users unless you do some mapping, or unless you increase the size of the userID columns everywhere.

If you want to reserve the first X numbers for whatever reason, you could set the auto increment value to X and new users will automatically start at X + 1. You could then manually change user IDs to those reserved ids with the last statements in my previous post.

Still a lot of work though.

How to restore or re number User ID after mass deletion

Comments