Join Log in
Score:0
avatar Drupal

Cannot add new user due to "Integrity constraint violation: Duplicate entry" error

in flag
Marshall Morrise

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 discovered that since that date, the site has been trying to reuse every ID starting with 1. My error log is filled with errors like this:

Drupal\Core\Entity\EntityStorageException: 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 Drupal\Core\Entity\Sql\SqlContentEntityStorage->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 would be fine if it were being used. But it's not.

131
0 + 0
9
Score:3
avatar Drupal
in flag
Marshall Morrise

After long looking, I noticed on a backup from before December 14, 2021, the users table uid field was defined as Attributes: UNSIGNED. NOW (February 18, 2022), the uid field is defined as Attributes: nothing and Extra: AUTO_INCREMENT. The AUTO_INCREMENT is new.

This led me to test with a backup copy of my site. Sure enough, prior to December 14, 2021, each time I add a user, the value in the sequences table is incremented. That no longer happens in the updated site. Instead, it crashes as described above.

The solution was to run the following query in the database:

ALTER TABLE users AUTO_INCREMENT = 3918; # highest existing ID was 3917

This solved the problem. I assume that in a core update installed on December 14, 2021, the code changed from using the sequences table to using AUTO_INCREMENT on the uid field in the users table and that somehow, I failed to do a database update that would have correctly set the AUTO_INCREMENT value in the users table to avoid this mess.

I hope this report of my pain will help someone.

0 + 0
Rod avatar
tr flag
Rod
Following an upgrade to Drupal 9.3.12, new users couldn't be created on the site. The error message I got when attempting to add a user either through the site or via drush was: "SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '220' for key 'users.PRIMARY...". Your ALTER TABLE query saved the day and fixed the issue for me. Thanks Marshall!
0
Reply
ธงชาติไทย
Elon Musk
I sit in a Tesla and translated this thread with Ai:

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.