5

When I check my users list via by ID, I get IDs like these:

  • User: admin
    Id: 1

  • User: second user
    ID: 11

  • User: another user
    ID: 21

I want them to be consecutive numbers from 1 to N.

What's my problem? How can I fix it?

2
  • 5
    Why would you want it in order? you have deleted some users, that is why the user ids are not in sequence – Shabir A. Dec 24 '14 at 19:31
  • Of course this is possible (running a update query against the users table) but then you also have to update alot of other tables that are linked to the first one. I wouldn't recommend it. – Cyclonecode Dec 25 '14 at 8:16
4

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.

1
  • Thanks Berdir, I've updated my answer to remove the auto_increment property after the update. Perhaps that will suffice... In any case, changing the UID is risky and not recommended. However, your input is appreciated! – David Thomas Dec 31 '14 at 21:27
4

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:

https://www.drupal.org/project/me

10
  • And nodes created by users? Of fields of profile, and any other information related to the user on other tables (first of all users_roles). – arrubiu Dec 25 '14 at 8:31
  • This could break a site and all contrib modules associated. One should not want to do this. – Neograph734 Dec 25 '14 at 15:57
  • 1
    Yes, I know its risky, see my first line, but the point of this site is to give answers and therefore I gave one. – David Thomas Dec 25 '14 at 20:42
  • 2
    And you were right to @David. But don't forget people are well within their rights to downvote if they don't agree something is a good idea. Personally I like "this is a bad idea, but technically this is how you do it..." type answers. Gives people the opportunity to learn 2 things instead of 1 – Clive Dec 25 '14 at 21:33
  • Thanks @Clive, yes it's hard to imagine the situation of the OP for these questions. Sometimes it might be very important for them to do something that's quite risky / not recommended. These types of technical 'not recommended but technically possible' answers can be just the ticket to save the day in such cases. I was actually curious myself, tested, and the above answer does work to reset the UID sequence. Cheers :) – David Thomas Dec 25 '14 at 22:30
2

No, you can't, or you should not. The user id is an autoincremental value of the users table in the database. And every information about the user (nodes, field values and any other thing) is related to the user with the UID.

2
  • Upvote for maling sure this should not be done. – Neograph734 Dec 25 '14 at 15:57
  • Should not be done and could not be done are two different things. – David Thomas Dec 25 '14 at 20:47
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.