Starting December 14, 2021, my site began creating users with low user IDs, even though the highest-used user ID was 9317. I thought something had been changed in a core update to reuse IDs that had been freed up by canceling a user account. However, I have just discovered that since that date, the site has been trying to reuse every single ID starting with 1. My error log is filled with errors like this:
DrupalCoreEntityEntityStorageException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '281' for key 'users.PRIMARY': INSERT INTO "users" ("uuid", "langcode") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 5ff1347c-439f-437e-ba3f-762f7350fda3 [:db_insert_placeholder_1] => en ) in DrupalCoreEntitySqlSqlContentEntityStorage->save() (line 811 of /home/drupal/webroot/rsc.byu.edu/web/core/lib/Drupal/Core/Entity/ Sql/SqlContentEntityStorage.php). The above example says Duplicate entry '381'. The next error will say Duplicate entry '382' and so on.
I read posts suggesting the sequences table contains the next user ID value. My sequences table contains the value 9330, which looks about right and would be fine if it were being used. But it’s not.
I hope someone can point me in the right direction with this.
UPDATE 1: After long looking, I noticed on a backup from before December 14, 2021, the users table uid field is defined as Type: int, Attributes: UNSIGNED, Null: No, Default: None. NOW (February 18, 2022), the uid field is defined as Type: int, Attributes: nothing, Null: No, Default: None, AND Extra: AUTO_INCREMENT. The AUTO_INCREMENT wasn’t there before this problem began happening.
Does anyone know whether these changes were part of a Drupal update that changed the way the next user ID is determined?
UPDATE 2: I tried the following command in the database:
ALTER TABLE user AUTO_INCREMENT = 3918; # highest used so far is 3917 This seems to have solved the problem. I’m hoping someone knows whether a change was made to move away from the sequences table to using AUTO_INCREMENT or if I’m just experiencing something weird.
