[Integrity constraint violation: 1062 Duplicate entry] On user registration

I had the same issue afterhaving updated to Drupal 7.2
Basically in the hook_user_insert I was assigning by code a specific role to the new created user.
To do that i was using a snippet of code like this

global $user;
$key = array_search('your role', $user->roles);
if ($key == FALSE) {
$roles = user_roles(TRUE);
$rid = array_search('your role', $roles);
if ($rid != FALSE) {
$new_role[$rid] = 'your role';
user_save($user, array('roles' => $new_role));
}
}

It was working smoothly with Drupal 7.0 after the update to 7.2 I had the error reported above.
SO I fixed replacing user_save($user, array('roles' => $new_role)) with a direct call to DB. (db_insert)
Thanks Mindexperiment for your advice!!!

At #4, you should try hook_user_presave() and add the role to the user object.

@Mindexperiment - I'm moving this to the Rules project since it looks like an issue there (or 7.2 may have broken Rules - but then we should let fago know).

Drupal 7.2 works with Rules and #5 sounds like the UI from Rules 1.x for D6. Please specify the exact event/action names in Rules 2 to reproduce this bug.

Ah, I see. This is similar to #1068168: Auto saving of a new node after the event node_insert is broken, where the is_new flag was a problem. The following patch fixes that issue, however, a problem with saving user roles still remains. user_save() calls hook_user_insert() before saving user roles, so Rules interferes and (auto-) saves the roles first and then user_save() tries save them again. This leads to another DB error.

Maybe this is a core bug, maybe we can solve it in Rules, I don't know right now.

A workaround is to use the event "Before saving a user account" and the condition "Entity is new" with the account variable.

Grml, need to correct myself: that workaround does not work for two reasons: the add role action is not allowed for anonymous users (which a new and unsaved user account is, uid == 0) and the user_save() function overrides changes to $account after hook presave with original values from $edit.

So all in all this is a huge mess. I have to say, I hate user_save() with a passion. Currently the Rules event user_presave is severely broken and will not save all possible changes to a user account. We will have to copy all properties of $account back to $edit so that the changes are picked up by user_save().

New patch that fixes the event user_presave. Remaining problems with user roles:

* user roles cannot be assigned during user_presave (because uid == 0)
* user roles are saved twice on user_insert which leads to a DB error

Maybe I'm mixing to many problems here, but I think they are all user related and it doesn't hurt to work out the nightmare with user_save() in one issue.

>Grml, need to correct myself: that workaround does not work for two reasons: the add role action is not allowed for anonymous users (which a new and unsaved user account is, uid == 0)

hm, sounds like we should fix the add-role action to work in case the uid is not set or is_new is set, so it works during creation.

>...and the user_save() function overrides changes to $account after hook presave with original values from $edit.
yes, but don't forget $edit is part of the user presave hook - so this is correct. Although I very much agree with you that user_save() sucks. ;)
Your work-a-round is fine, but it duplicates the work user_save() already does. So I think it would be better to just use the entity-level hook in that case.
(I guess we could migrate lots of event invocation to the entity-level hooks anyway).

>* user roles are saved twice on user_insert which leads to a DB error
Yes, that's weird and a bad behaviour of user_save(). I think the easiest work-a-round for that would be special-casing the add-user-role action to not trigger a save if we it got triggered from user-insert. For that, perhaps it would be better if we keep $user->is_new and just make sure we set it to FALSE any-time before we invoke auto-saving for an entity which has already an id.
Sounds correct to me, and fixes such issues potentially for any entity.

Alright, new patch that includes all that proposed changes. Assigning roles now works both for user_presave and user_insert.

Still open: remove role action, should be similar.

Whoops, new patch that leaves the function signature of rules_action_user_add_role untouched.

Patch looks already good.

@remove-role: I don't think it causes problems, but to be sure I think we should prevent saving during user-creation-hook-insert as for the add action.

+    if ($account->is_new && $account->uid) {
+      // Disable saving for newly created accounts, otherwise we get ugly DB
+      // errors because user_save() also tries to save the roles.
+      return FALSE;

is_new does not need to be set, so this could trigger notices.

Added an !empty() check to all places where is_new is accessed. Added the same checks to the remove roles action.

thanks klausi, I tried my best to further improve the comments and committed it (as the tests are green).

The committed patch fixed this issue where the action being carried out was "Add user role."

However, before the patch I was also getting the same errors when I set the action to "Block a user" and I now get the following error instead:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '56-4' for key 1: INSERT INTO {users_roles} (uid, rid) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 56 [:db_insert_placeholder_1] => 4 ) in user_save() (line 579 of /path/to/user.module).

Ignore the above. It seems to be a conflict with another module. I'll open another issue if necessary.

Hey all

When I try to patch it always says "can't find file to patch at input line 5". I tried it with the following command:

patch -p0 --dry-run < /var/www/drupal7/1169800-rules-user-save-mess_2.patch

I'm in the path /var/www/drupal7/sites/all/modules/rules when i try this. Does anyone have a suggestion? Thank you xD

Edit: I'm using drupal 7.2 and get allways the error at the top of this topic.

1) you can't apply the patch, as it has already been committed.
2) we use "git apply" to apply patches: http://drupal.org/node/707484

I have found a new bug within this issue.

If you perform more than one action on a new user, you still get a "PDOException: SQLSTATE[23000]: Integrity constraint violation ... " in user_save() (... /modules/user/user.module)

Try it yourself:
Event - After saving a new user account
Action - Add user role
Action - Unblock a use (should be user?)

The above will cause an error, while having just one action won't.

Please try using the latest dev versions + open a new issue (optionally referring to this one).

I'm using the dev and still experiencing #25.

Same here. #25.

FWIW, we needed to run multiple actions on new user registration and ended up just doing it in a custom module using hook_user_insert (http://api.drupal.org/api/drupal/modules--user--user.api.php/function/ho...) to get past this issue.

I had an action to unblock a user, execute some custom PHP, and add a specific role if the user passed a certain (custom PHP) condition.

Putting both the "unblock a user" and "add a role to user" failed with the above behavior (even tried changing the order of the actions, and tried a data action as well to no avail). To workaround the issue I added the "unblock a user" to my custom PHP code.

So now the event works:

Event: After saving a new user, Condition: Custom PHP code, Action: Add a user role, Custom PHP code(includes the task I have defined and at the bottom I added code to set the user's status = 1).

Another workaround: if you convert the UID to a variable in rules, you can then convert that UID variable back into a user variable and perform another action on that user.

I'm encountering what I think is the same issue, but this fix doesn't work. I had the 7.x-2.0 installed, which was released before this issue arose and the patches which according to comments above were rolled into the dev version. So, I installed the latest dev version a day or so ago, and ran update.php; but the error continues to occur.

Is there another fix for this?

Every time cron runs, I get the following error:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'name': INSERT INTO {users} (uid, name, pass, mail, theme, signature, signature_format, created, access, login, status, timezone, language, picture, init, data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9, :db_insert_placeholder_10, :db_insert_placeholder_11, :db_insert_placeholder_12, :db_insert_placeholder_13, :db_insert_placeholder_14, :db_insert_placeholder_15); Array ( [:db_insert_placeholder_0] => 40200 [:db_insert_placeholder_1] => [:db_insert_placeholder_2] => [:db_insert_placeholder_3] => [:db_insert_placeholder_4] => [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => 2 [:db_insert_placeholder_7] => 0 [:db_insert_placeholder_8] => 0 [:db_insert_placeholder_9] => 0 [:db_insert_placeholder_10] => 0 [:db_insert_placeholder_11] => [:db_insert_placeholder_12] => [:db_insert_placeholder_13] => 0 [:db_insert_placeholder_14] => [:db_insert_placeholder_15] => a:1:{s:7:"contact";i:1;} ) in drupal_write_record() (line 6975 of /path-to-drupal/includes/common.inc).

Could you describe the rule that causes this?
It's probably the same issue like all the other 23000 errors: trying to do multiple actions on a new user, node, ...

Thanks for getting back to me. I don't know what rule it is; I didn't know it was related to Rules until I found this post.

It gets triggered like clockwork every single time cron runs. That's when it's seen the most.

Duplicate entry '' for key 'name': INSERT INTO {users}
This bit tells me you're trying to do stuff with a new user.

Look at #25 for steps to reproduce and #31 for a workaround.

Thanks for getting back to me on this. I just saw your reply today, even though I had been checking my dashboard for the past week -- I wish D.o had notifications for followed issues!

I looked at #25 and #31, but I am still in the dark on this. I get that it's about a new user and something going wrong there, and from this post I understand it has something to do with Rules' behavior. And I get that it's a Rule being triggered directly or indirectly by cron, since it happens every time cron is run.

However, that's all I get. I don't get what rule to look for. I don't get how I would change if I found it.

Sorry to be daft. Can someone hand hold me idiots-guide style through this one? I'm really trying to finally exorcise some mystery performance issues and lingering nasty little bugs. I'd like this demon gone from my system as soon as possible, if someone can just give me the magic words…

Thanks in advance!

Go to admin/config/workflow/rules and look for any rule that does something to a user.
In your case, pay special attention to the ones with the event 'Cron maintenance tasks are run'.

If any of those tasks do more than one edit to a user, try to create actions in that rule which:

• Perform first user action
• Add a variable (convert the user to a UID (integer) variable)
• Add a variable user (convert UID back to a user)
• Use the second variable for your second user action

Looks like I have a similar problem:
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '' for key 'name': INSERT INTO {users} (uid, created) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1); Array ( [:db_insert_placeholder_0] => 623 [:db_insert_placeholder_1] => 1332882002 ) in drupal_write_record() (line 6888 of */public_html/includes/common.inc).

I get two of these every time Cron runs.
None of my Rules are specifically triggered by Cron but I do have some Scheduled Components.
The messages go away when I disable Rules Scheduler.

None of my components do anything to Users besides sending emails and no single component does this more than once.
I get the messages even when the emails are not triggered.

I'm encountering this issues also. I don't understand how to implement the workaround in #31. @kristiaanvandeneynde, would you mind explaining how you would do this with an existing rule, such as a rule to grant a user a role upon registration? In which part of the rule do you do this conversion? In which part do you convert it back?

@Spry_Julia This would unblock a newly registered user and add a role.
Normally such a thing would cause the PDOException: SQLSTATE[23000], but with this workaround it doesn't.

Event
After saving a new user account

Conditions

• User is blocked - Parameter: User: [account]

Actions

• Add user role - Parameter: User: [account], Roles: CaptainAwesome
• Add a variable - Parameter: Type: Integer, Value: [account:uid] - Provides variables: Acc id (acc_id)
• Fetch entity by id - Parameter: Entity type: User, Identifier: [acc-id] - Provides variables: Acc ent (acc_ent)
• Unblock a user - Parameter: User: [acc-ent]

+1

@kristiaanvandeneynde Thank you!!

The same problem is with node (after saving new content).

I was also getting the PDO Exception message. I temporarily worked around it be adding a "Create entity" action to create a node and then immediately deleting that node with a "Delete entity" action. I have no idea why this works but it resolved the problem for me.

Confirms that workaround at #40 is functioning. Thanks kristian!

> I have found a new bug within this issue.
Then you should create a new issue and link back to this one.

Please don't reopen this issue. It won't help solve the problem. Sorry for the bluntness, but this is the workflow we use to identify individual issues and commit fixes for them.

Done: #1771666: Integrity constraint violation when saving a user account after creation

Problem:
Create user-entity (OK)
Set user-entity fields (OK)
Set user-entity role (ERROR)

Workaround:
Create user-entity (OK)
Set user-entity fields (OK)
Save user-entity (OK)
Add variable type user-entity with the created user as value (OK)
Set user-entity role (OK)
Save the new user-entity (OK)

@guilherme Do you need to provide the variable after the save user-entity? Because in my case I am using provides in my custom rule, which means that the variable is provided before Save.

It seems like your work around isn't working for me.

Could the problem principle be similar to this one concerning node issues (already fised by the core fix) ?

https://drupal.org/node/1146248

I have the same Issue as #4 with the same code

db_insert didn't help in my case and

I tried this code as well
http://www.computerminds.co.uk/articles/quick-tips-adding-role-user-drup...
but it doesn't work.

I have the issue when I create a user with the same role that I want to add via this snippet

If I do a dsm($user) before the call to
user_multiple_role_edit
then it works.

strange...

I have the same issue, using field collection events. The rules were about setting a data value to a taxonomy reference field depending on the number entered into a decimal field, within the same field collection.

The first time I created the rules (about 3 or 4 of them), they was working perfectly fine. But when I tried cloning them and tweaking the data selector values, the 1062 constraint started popping up, keeping me from creating new nodes with the embedded field collection.

I then disabled all these rules and the error disappeared. Now when I try re-enabling just one of these rules, the error appears again. I noticed that every time it shows up, they "key" is incremented. I looked at the {node} table values and the nids that succeeded every time the error shows up doesn't even exist in the table yet.

So this "duplicate" entry issue is quite weird.

What I'll do is try to delete my rules, uninstall the Rules module, and enable it again and create the rules again. Since it worked the first time and this error didn't annoy me at first, maybe I could try to recreate it and see in the db which table might be causing the core to report this integrity constraint.

Working off of #48, this issue cleared after adding "Save Entity -> User" to the Rule's Actions. Just adding the role without also saving the user in the Rule triggered the error for me on cron runs. Should also note my Rule fired on a Webform submission.