PDOException: SQLSTATE[23000]: Duplicate entry in {node_access} table: when assigning a workflow to the node

I've had the same problem on my site and a bit of investigating has shown that it seems to happen only for users with the "administrator" role. Users with lower degrees of site access seem to be able to create new nodes without seeing this error.

I guess this points to a problem having to do with role access, permissions, or something related specifically to the administrator role in the Workflow module?

Update to my last post: I'm no longer sure that it's linked to user roles. At first in my testing this appeared to make a difference, but now the error appears to be occurring more intermittently even when a test user's role stays fixed.

The same is happening to me. The query is being executed twice, that's why is giving a 'duplicate entry' error. I have no idea why this is happening. I have another node types with another workflow assigned and they work ok.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '737-1-workflow_access' for key 'PRIMARY': INSERT INTO {node_access} (nid, realm, gid, grant_view, grant_update, grant_delete) 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); Array ( [:db_insert_placeholder_0] => 737 [:db_insert_placeholder_1] => workflow_access [:db_insert_placeholder_2] => 1 [:db_insert_placeholder_3] => 1 [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => 0 [:db_insert_placeholder_6] => 737 [:db_insert_placeholder_7] => workflow_access [:db_insert_placeholder_8] => 2 [:db_insert_placeholder_9] => 1 [:db_insert_placeholder_10] => 1 [:db_insert_placeholder_11] => 1 ) a node_access_write_grants() (línia 3392 de /var/www/drupalruy/drupal7base/modules/node/node.module).

Definitely, it's a problem with workflow_access module. If you don't need access control based on workflow, just disable this module to avoid the problem until it's solved. If you need role based access control you can use content access module.

Can both of you try something for me with regards to this? Try rebuilding your node access permissions ( www.yoursite.com/admin/reports/status/rebuild should be the right link ) to see if that will kick it over to operating properly. Let me know if that works.

I'm experiencing the same problem here. Only thing that helped was to deallocate the workflow from the node type -> not so good :(

Hi slashdot67. In my site I have different "workflow state groups", for example "quotes states" with "open, closed, sent, etc.." and "invoice states" with "accounted, paid, etc... I only have problems with "quotes", I mean, when I try to create any quote. The configuration for both state groups is identical.

Hi Bastlynn, this is the first thing I tried when saw the bug, but nothing changed.

The only thing that worked for me was, disabling the workflow_acces module or, as dsjena says, deallocating the workflow from the node type.

Subscribing to this!

For me, the error only shows when there is a rule set up where the event is 'After saving new content' and the action is 'Set workflow state'. It does not appear when the condition in the rule is not met. It does not appear when the event is changed to 'After updating existing content'. I hope this helps to get this fixed soon.

I tried several combinations of permissions, rules conditions and events, and finally the only thing that got rid of the error was assigning no roles that could view, edit or delete the content on the first workflow state.

workflow_access.module, line ~ 176 in 7.x-1.0

function workflow_access_workflow($op, $old_sid, $sid, $node) {
// ALERT:
// This is a tricky spot when called on node_insert as part of the transition from create to state1.
// node_save invokes this function as a hook before calling node_access_acquire_grants.
// But when it calls node_access_acquire_grants later, it does so without deleting the access
// set when calling workflow_node_insert because it is an insert and no prior grants are expected.
// This leads to a SQL error of duplicate grants which causes a rollback of all changes.
// Unfortunately, setting access rights isn't the only thing we're doing on node_insert so we
// can't skip the whole thing. So we need to fix it further downstream in order to get this to work.
// Here we don't want to run this in the case of (and ONLY in the case of) a brand new node.
// Node access gratns will be run as part of node_save's own granting after this.
//
// NOTE: Any module that sets node access rights on insert will hit this situation.
//
if ($old_state = workflow_get_workflow_states_by_sid($old_sid)) {
if ($op == 'transition post' && $old_sid != $sid && $old_state->state !== '(creation)') { <===== THIS MAY BE TRANSLATED!!
node_access_acquire_grants($node);
}
}
}

now change line 191 to

if ($op == 'transition post' && $old_sid != $sid && ! $node->is_new ) { <======== is_new is a boolean property

It fixed the error for me.

Hope it helps.

Best regards!

Thank you very much Leonardo !
Here is the patch file if needed.

The previous patch sends a warning when used with VBO and Worflow actions.
Notice: Undefined property: stdClass::$is_new in workflow_access_workflow() (line 191 of /home/www-preprod/sites/all/modules/workflow/workflow_access/workflow_access.module).

Here is a new one.

Excellent work! I didn't know about the node is_new value. Really really useful for this situation. Patch is committed on the 7.x-1.x-dev branch.

I have the same error and all stated workarounds are useless for me. I have a very complex D7 site with a custom content-type and a workflow for it. A rule check if a new node is created, then should set the workflow state to the first step (out of 7).

Moreover I don't understand how "$old_state->state !== '(creation)" could be turned into "(empty($node->is_new) || !$node->is_new))": they seem to me different conditions (couldn't be a node "not new", but with old_state=='(creation)'? So first syntax would be FALSE and second TRUE)... anyway, as I said, it doesn't work for me.

Nodes should never be stored with a state of (creation). The creation state represents the actual event of creating the node, so the second the node is saved it's no longer being created and is automatically updated to the first state in the workflow from creation. So in this case - no a node that has already been created cannot / should not also have a creation state associated with it. If it does, then something has gone very wrong.

Since the state it goes to is the first state listed in the workflow, I don't think you need to add the rule on creation. In fact, that rule may be what's causing the problem for you if you are also saving the node during that update. Doing that triggers a save, before the system can complete the workflow save, which could cause a conflict in the access table as it tries to save it twice. Turn off that rule and see if that works?

Mmmh... maybe something went wrong, as nodes were created without switching workflow state to the first one. Now it's going well and the action is necessary no more. Thank you.

Huh, weird. Ok :) I'm glad it's gotten sorted out now at least.

I too was getting this error when using Workflow and Rules to moderate a custom content type (Song) on a fairly vanilla Drupal 7.12 installation.

I had a workflow with the following states:
(created)
Submitted
Accepted
Completed

I created a rule that is as follows:
Event: 'After saving new content'
Condition: Data comparison - 'Parameter: Data to compare: [node:type], Data value: Song'
Action:Set workflow state for content- Parameter: Node: [node], New workflow state: Submitted

Upon creation of a new node that had the workflow associated with it, I would get the error cited above, but I could see that the content was indeed being created. Bastlynn is saying that nodes should never be stored with a state of (creation) and that you shouldn't need to make a rule (like I did) that would take the new node and update it to the first state, in my case 'submitted'. The reason I created this rule though is because without it, newly created Song nodes would just site there stateless (I experienced the same things as AndyNaimoli). However, now that I deleted that rule, I see that I'm no longer getting the error AND the Song nodes are now automatically moving to a Submitted state. So I have no idea why it is fixed but wanted to confirm that I experienced the same weirdness as AndyNaimoli. Not very helpful I guess, but food for thought.

I'm glad my experience is not so weird, after all! Thankx kristin.brinner for your comment...

My apologies if some consider this necromancy.

The problem that I had was indeed using a condition that checked draft workflow state, and unpublishing that content (if moved from the approved state by an administrator). My solution was to change the condition from a data comparison, to "check workflow transition". The improved logic removed the error that was being generated.

I was getting this error and figured out that this problem occurs when a translation is installed.

I have changed the following line on workflow_access.module (line: 191) from
if ($op == 'transition post' && $old_sid != $sid && $old_state->state !== '(creation)') {
to
if ($op == 'transition post' && $old_sid != $sid && $old_state->state !== t('(creation)')) {
and it was fixed

#12 Worked for me. I had a different Rules action that set a value after a node was published, but it seems like changing it to "Before Saving Content" removed the error.

I have the same problem and the patch of #16 doesn't work.
I have the latest version of drupal & workflow.

The problem occurs during the execution of a Rule with a "After saving new content" event (no problems with "Before saving content", as someone said before).
My Rule is about cloning a node, It doesn't touch to the workflow states.

Here the message :

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '98-9-workflow_access_owner' for key 'PRIMARY': INSERT INTO {node_access} (nid, realm, gid, grant_view, grant_update, grant_delete) 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); Array ( [:db_insert_placeholder_0] => 98 [:db_insert_placeholder_1] => workflow_access_owner [:db_insert_placeholder_2] => 9 [:db_insert_placeholder_3] => 1 [:db_insert_placeholder_4] => 1 [:db_insert_placeholder_5] => 1 ) in node_access_write_grants() (line 3417 of /home/bobo/Documents/Programmation/Web/drupal-7.14_test/modules/node/node.module).

Thank you for your help

edit :
I've deleted actions of my rule one by one and finally the error occurs when the action "set a data value" is set.
This action set a field of the node just created, it's a reference to another content type which is stocked in the current-user variable.
This field is meant to receive this kind of content and the variable in current-user is well set.

After other tests, the error occurs as soon as my Rule touch to one of the just created node's fields, I tried with 3 types : Reference, term reference, text.

As #13 if I nobody can view,edit or delete on the first state, the error doesn't occur... that's a bit annoying :)
Actually that's not enough, the author also need the right to change the state from creation to the first state, otherwise the error occurs.

@Ryssbowh
did you try #28 ?
this worked for me
...

#27 is right.
This problem is due the translation of string '(creation)'.
After deleting translation of this string the problem has gone.

You may take a look at
#1475930: Do not rely on (creation) state name

correct! i deleted the duplicate states (heck, i had two "creation" and one "criação) and the problem as no more

close issue?

I found a link on the Rules page to a thread in the core issue queue with a patch to the D7 core node module that fixed the issue for me when using pretty much any access control module - tested successfully with Workflow Access, Content Access and Private. It doesn't fix the translation issue but it does take care of problems with Workflow Access.

It should get rolled into the next core dev release, but until then I hope it helps out a few people - took me two days to find it.

http://drupal.org/node/1146244

-=Delty

My error solved with the patch specified in #34

i was able to resolve this with workflow 7.x-1.0 using patch #3 from #1475930: Do not rely on (creation) state name and patch #65 from #1146244: node_access integrity constraint violation on module_invoke_all('node_' . $op, $node);.

that second issue has been fixed for drupal 8.x but the drupal 7.x solution is still being debated. i chose a patch which seemed to make only minor changes for now so that my site would work. once that issue is resolved i'll go back and use the correct solution while waiting for drupal 7.18.

Not sure if this helps anyone, but wanted to document here what worked for us to fix the below error we were getting after saving new content that had workflow assigned to it.

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '218-3-workflow_access' for key 'PRIMARY': INSERT INTO {node_access} (nid, realm, gid, grant_view, grant_update, grant_delete) 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, :db_insert_placeholder_16, :db_insert_placeholder_17), (:db_insert_placeholder_18, :db_insert_placeholder_19, :db_insert_placeholder_20, :db_insert_placeholder_21, :db_insert_placeholder_22, :db_insert_placeholder_23); Array ( [:db_insert_placeholder_0] => 218 [:db_insert_placeholder_1] => workflow_access [:db_insert_placeholder_2] => 3 [:db_insert_placeholder_3] => 1 [:db_insert_placeholder_4] => 1 [:db_insert_placeholder_5] => 1 [:db_insert_placeholder_6] => 218 [:db_insert_placeholder_7] => workflow_access [:db_insert_placeholder_8] => 8 [:db_insert_placeholder_9] => 1 [:db_insert_placeholder_10] => 1 [:db_insert_placeholder_11] => 1 [:db_insert_placeholder_12] => 218 [:db_insert_placeholder_13] => workflow_access [:db_insert_placeholder_14] => 4 [:db_insert_placeholder_15] => 1 [:db_insert_placeholder_16] => 1 [:db_insert_placeholder_17] => 1 [:db_insert_placeholder_18] => 218 [:db_insert_placeholder_19] => workflow_access_owner [:db_insert_placeholder_20] => 5 [:db_insert_placeholder_21] => 1 [:db_insert_placeholder_22] => 1 [:db_insert_placeholder_23] => 1 ) in node_access_write_grants() (line 3417 of /var/www/vhosts/DOMAINNAME/httpdocs/modules/node/node.module).

In our case the solution we used to apply in Drupal 6 worked again in Drupal 7: if using Workflow module, allow Workflow Access module to control access to the nodes - do not use the Rules to publish or unpublish content.
In particular:
1. Set this content type to be published (content type > edit > publishing options > published)

2. Set workflow rules to allow authors + special roles (administrators) to view/edit nodes when in workflow states "Draft" or "For Review". Allow anon and authenticated users view only nodes when in "Published" state. NOTE: state names are for demonstration purpose only, please use your own names

3. Do not create Rules that would publish or unpublish nodes of this content type based on the workflow transition - let Workflow Access worry about it.
You can still use the Rules to check the workflow transition and do other things like sending emails, displaying messages etc.

That fixed for us the error above - no more conflicts between node access and workflow access.

It seems to be largely related to #1475930: Do not rely on (creation) state name, which has been committed. Can someone test again with the current -dev, please?

See also #1648904: Workflow Access control Module doesnt allow user to save content
and #1801438: Error with rules updating node

Subscribed and #16 confirmed as working.

Worked without the patch on a non-multilanguage site but not with translatable nodes.

May I suggest that this fix gets into a release quite soon as it would be nice not to have to work with a patched/dev version? Thx!

I had this problem when trying to upgrade from Drupal 6.28 to Drupal 7.19. philsward also posted on this back on December 12th.

My PDOException [SQLState 23000] Integrity constraint violation 1062 was not due to workflow but to permissions issues. Here is what I found out:

1. The reason for the exception is that I was trying to insert two entries with the same (role,permission) combination into the new role_permission table. (role,permission) is identified as the primary key in the schema for the role_permission table, so every entry must be unique.

2. How did this happen? When I looked at my Drupal 6 permission table, there were no duplicate permissions in any of the roles that I had defined.

3. HOWEVER, when I looked at the permission table in Drupal 7 [which I could do because update 7007, which creates the new role_permission table, was failing, and thus not dropping the permission table] I found that some entries in the Drupal 6 permission table had been changed. Specifically, the Drupal 6 blog module's 'create blog entity' and 'edit own blog entity' permissions had been CHANGED to 'create blog content' and 'edit own blog content'. These then conflicted with the node module, which also has 'create blog content' and 'edit own blog content' permissions.

4. I HAVE NO IDEA HOW WHERE OR WHY THIS CHANGE HAPPENED.

5. The fix I came up with was inelegant, I'm sure, but I want to get my site up, so I just went into the Drupal 7 version of the permissions table and deleted the duplicate permissions. Once I did that, the update went through just fine.

So the suggestion is that if you are seeing this kind of error, look for duplicate entries in the source table that Drupal 7's update is using to create its data base.

Wyckham

#12 working in my case;
Anonymous creating node, Rules makes it Unpublished and sets a workflow state. Changing Event to "Before saving content" removed the errors.

I'm having this problem too. I have 2 workflows, each for a different contenttype.

I noticed that the workflow I created first, has the possibility the change the (creation) status. However, the other workflow does not give that possibility.

I also have 'Node privacy by role' installed. But the node access settings for both contenttypes are the same.

I just found the solution for my issue:

My website is in Dutch and I installed the Dutch po-files for all modules too.
Seems that Workflow saved the '(creation)' status in Dutch in the database. Because of that, it didn't find the '(creation)' status. I changed (aanmaak) to (creation) and now it all works fine...

There should never be nodes in the creation state. There must be at least one state which the author has access to. The node will then move into that initial state.

Can you locate the code that incorrectly translated "(creation)" before storing it in the database? That needs to be corrected.

As far as I can tell this should be fixed in 7.x-1.1. If it is not, please open a new issue.

I am still getting this error in 1.1

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '196-1-workflow_access' for key 'PRIMARY': INSERT INTO {node_access} (nid, realm, gid, grant_view, grant_update, grant_delete) 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); Array ( [:db_insert_placeholder_0] => 196 [:db_insert_placeholder_1] => workflow_access [:db_insert_placeholder_2] => 1 [:db_insert_placeholder_3] => 1 [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => 0 [:db_insert_placeholder_6] => 196 [:db_insert_placeholder_7] => workflow_access [:db_insert_placeholder_8] => 2 [:db_insert_placeholder_9] => 1 [:db_insert_placeholder_10] => 0 [:db_insert_placeholder_11] => 0 ) in node_access_write_grants() (line 3551 of /srv/www/efacecusa.com/drupal2/modules/node/node.module).

Similarly to #12

"For me, the error only shows when there is a rule set up where the event is 'After saving new content' and the action is 'Set workflow state'. It does not appear when the condition in the rule is not met. It does not appear when the event is changed to 'After updating existing content'. I hope this helps to get this fixed soon."

For me, the error went away when I disabled a rule that was used for the event "Workflow state has changed" where I publish an article automatically if the "content has a workflow state" of approved. That's what is causing the error.

See #47 - please open a new issue.

There was another issue with a similar message where Rules was trying to publish a node that was already published. Do you check for that in your rule?

Similar to #12. The error disappeared when I changed the rule event from 'After saving content' into 'Before saving content'.

format post

#12 work for me.Anonymous creating node, Rules makes it Unpublished like #43.