MyBB Community Forums

Full Version: How to remove unused uid's?
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
So I made several temp accounts because my php mail wasn't working and then I deleted them all. Now there's unused uid's and when a user registers it says there uid is 60 something when there's only 53 registered members... I want to delete these cause it bugs me so much but I'm not sure how to remove them Sad Please help.
The uid is automatically generated by your database when an user registers by incrementing the corresponding field in the _users table. I would not recommend doing this unless you are running a test board and you know what you are doing. Save a backup of your database before continuing, then:

ALTER TABLE mybb_users AUTO_INCREMENT = 1

By running this command in your SQL manager (typically PHPMyAdmin) you reset the counter of mybb_users table. If you have another database prefix, replace mybb_ with your prefix.

If you are using InnoDB as your storing engine, you must replace "1" with the highest uid you have in the table plus 1. So if you have 53 as your highest number, you would replace "1" with 54. That's because MyISAM engine will set the counter to the last one plus 1 automatically, while InnoDB does not.
Instead of creating new UID's by using the last highest UID +1, just use the first unused UID.

If you have UID's  1 2 3 4   6 7 8, a new user would use 5 as their UID then the next new user would use 9.

Is there any way of doing this?
(2018-05-28, 12:08 PM)alwilliamsmo Wrote: [ -> ]Is there any way of doing this?

You don't want to do this. It's not done this way for reasons.

UIDs don't matter whatsoever. Stop caring about them.
Yes, you'll enter into a world of pain trying to manually manipulate auto increment IDs. You're gonna get the same for deleted posts, deleted users, forum IDs, anything like this. The IDs are used across many other tables, so you'd have to update those, and you'll go down the rabbit hole and end up with all your data linking to the wrong thing. As frostschutz said, auto increment IDs are there to give each row a unique identifier so that it can be referenced in other tables, they're not intended to be pretty or consistent or to give you nice sequential numbers - other than in URLs (unless using Google SEO), you never even see them.