I have a site (7.18) in which I want to grant teachers access to assignments created by their students. My current solution is to set a teacher for each student, using a User Reference field. I then use a Rule to propagate the teacher setting to a field on each node the student creates, and I use the Node Access User Reference module to give access to the user referenced in the teacher field.
This should work fine, but whenever a student creates a node, I get the error message PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '*X*-*Y*-nodeaccess_userreference' for key 'PRIMARY', where X is the auto-increment ID for the next node that should be created, and Y is the user ID for the teacher (see the paste linked below for the full error message).
The full error references some code in node.module (lines 3427-3440). I got it to provide a dump of the queries it's doing (also in the paste below), and it seems that the function to insert access permissions in node_access is being called twice with (almost) the exact same parameters: First it does one query that inserts rows granting access to the admin account, the author, and the designated teacher, then it attempts to do the same thing over again.
This creates a key conflict in the node_access table. After that it presumably does a rollback, because the permissions (and the node itself) don't appear even once, but the auto_increment value for the node table does increase.
I've verified that this only occurs when the node access user reference effect is invoked. A student who does not have a teacher assigned can create nodes with no problem, and removing the node access for teachers from the relevant content type also eliminates the problem.
Any idea why this is happening, or how I can fix it? The quickest solution that comes to mind is to change node_access inserts to use insert if not exists but I'd rather not hack a core module in such an ignorant and potentially far-reaching way.
The error messages and database queries are the following.
ERROR MESSAGE
PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '17-4-nodeaccess_userreference' 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); Array ( [:db_insert_placeholder_0] => 17 [:db_insert_placeholder_1] => nodeaccess_userreference [:db_insert_placeholder_2] => 4 [:db_insert_placeholder_3] => 1 [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => 0 [:db_insert_placeholder_6] => 17 [:db_insert_placeholder_7] => nodeaccess_userreference [:db_insert_placeholder_8] => 1 [:db_insert_placeholder_9] => 1 [:db_insert_placeholder_10] => 0 [:db_insert_placeholder_11] => 0 [:db_insert_placeholder_12] => 17 [:db_insert_placeholder_13] => nodeaccess_userreference_author [:db_insert_placeholder_14] => 2 [:db_insert_placeholder_15] => 1 [:db_insert_placeholder_16] => 1 [:db_insert_placeholder_17] => 1 ) in node_access_write_grants() (line 3440 of /Users/ghostlyTrickster/Sites/drupal3/modules/node/node.module).
Dump of query object
InsertQuery_mysql Object
(
[table:protected] => node_access
[insertFields:protected] => Array
(
[0] => nid
[1] => realm
[2] => gid
[3] => grant_view
[4] => grant_update
[5] => grant_delete
)
[defaultFields:protected] => Array
(
)
[insertValues:protected] => Array
(
[0] => Array
(
[0] => 23
[1] => nodeaccess_userreference
[2] => 4
[3] => 1
[4] => 0
[5] => 0
)
[1] => Array
(
[0] => 23
[1] => nodeaccess_userreference
[2] => 1
[3] => 1
[4] => 0
[5] => 0
)
[2] => Array
(
[0] => 23
[1] => nodeaccess_userreference_author
[2] => 2
[3] => 1
[4] => 1
[5] => 1
)
)
[fromQuery:protected] =>
[connection:protected] => DatabaseConnection_mysql Object
(
[shutdownRegistered:protected] =>
[target:protected] => default
[key:protected] => default
[logger:protected] =>
[transactionLayers:protected] => Array
(
[drupal_transaction] => drupal_transaction
[savepoint_1] => savepoint_1
)
[driverClasses:protected] => Array
(
[SelectQuery] => SelectQuery
[DatabaseTransaction] => DatabaseTransaction
[InsertQuery] => InsertQuery_mysql
[UpdateQuery] => UpdateQuery
[MergeQuery] => MergeQuery
[DeleteQuery] => DeleteQuery
)
[statementClass:protected] => DatabaseStatementBase
[transactionSupport:protected] => 1
[transactionalDDLSupport:protected] =>
[temporaryNameIndex:protected] => 0
[connectionOptions:protected] => Array
(
[database] => lilacpro_drupwlk
[username] => drupal3
[password] => hunter2
[host] => localhost
[port] =>
[driver] => mysql
[prefix] => Array
(
[default] =>
)
)
[schema:protected] =>
[prefixes:protected] => Array
(
[default] =>
)
[prefixSearch:protected] => Array
(
[0] => {
[1] => }
)
[prefixReplace:protected] => Array
(
[0] =>
[1] =>
)
)
[connectionTarget:protected] => default
[connectionKey:protected] => default
[queryOptions:protected] => Array
(
[target] => default
[return] => 3
)
[uniqueIdentifier:protected] => 50edab60008ef7.00761431
[nextPlaceholder:protected] => 0
[comments:protected] => Array
(
)
)
NOTE: The second query is exactly the same as the first, except that the uniqueIdentifier is different, and connection->transactionlayers['savepoint1'] is missing.
