When I check my users list via by ID, I get IDs like these:
User: second user
User: another user
I want them to be consecutive numbers from 1 to N.
What's my problem? How can I fix it?
Drupal Answers is a question and answer site for Drupal developers and administrators. It only takes a minute to sign up.Sign up to join this community
The user ID in Drupal 7 and 8 is not an autoincrement, it is based on db_next_id(). That means it is shared with e.g. batch ID's and is the reason it does not count up like node IDs.
Changing this is close to impossible, you would not only have to manually update the users, the users_roles, all user field tables, all references to users (like node and comment author, entity reference fields, and so on) but you would also have to reset the global sequences table for db_next_id(), which could break other places in Drupal if they've been using db_next_id() and are relying on it to be unique.
So, no, you can't.
While updating the user id is not recommended, for arguments sake, you could reset the user id column in the database.
This is with the caveat that you must also update all referencing tables that use that user id so that those tables also use the new user id value - to maintain "referential integrity". Otherwise, if you do not update all the related user ids, you will corrupt and mixup private user data
For example, the
users_roles, file_managed, node, node_revision and other tables...
To match the users with the new uid, you could make an export first of user id to email address. Then you can make another export later and cross-reference the uid from the mail address - to check the new user id match. Keep in mind email address isn't strictly unique in drupal by default.
SELECT uid, mail FROM users;
Backup the users table first.
Then you could do something terrible like:
ALTER TABLE users DROP COLUMN uid; ALTER TABLE users ADD uid INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
That will obliterate and regenerate the users uid column.
UPDATE: As clarified by Berdir, curiously the user id field is not an autoincrement field in Drupal 7 by default. So, you may need to remove the autoincrement property after the UID sequence is recreated and convert to normal int with:
ALTER TABLE users MODIFY uid int(10) unsigned;
Note: you will need to regenerate the anonymous user account with:
INSERT INTO users (name, pass, mail, theme, signature, language, init, timezone) values ('', '', '', '', '', '', '', ''); UPDATE users SET uid = 0 WHERE name = '';
If you just want to hide the uid field value, you could instead try a user alias module like:
The fact that UID auto-increments and Batch API auto-increments are shared is a shortcoming. For one project I am developing, I am considering adding a serial field and attach it to the User profile. This way I will have logical incremental & consecutive reference to site's users.
I have just had a jump from user id 14 to 1123 without deleting any user, only due to the shared auto-increments.
It always amuses me when some people arrogantly state that "it cannot be done" and "should not be done" without fully understanding what they are talking about.
Changing a user's UID CAN be done and sometimes SHOULD be done. In my case, while migrating a database, its state became inconsistent because of a user's UID not matching its content. And instead of changing UIDs in thousands of nodes, it was much easier to change it on a users table.
Fixing it with drush was pretty easy:
drush sqlq "update users set uid=4 where uid=6"
Might have to do the same with the
users_roles table as well, and other tables depending on modules that impact the users tables (eg. access control, etc.)
If you're not fixing consistency issues, you should have a very good reason for changing an UID and know what you are doing, or you may leave your database in an inconsistent state.