Excerpt from private_nodeapi():

    case 'insert':
    case 'update':
      db_query('UPDATE {private} SET private = %d WHERE nid = %d', $node->private, $node->nid);
      if (!db_affected_rows()) {
        db_query('INSERT INTO {private} (nid, private) VALUES (%d, %d)', $node->nid, $node->private);
      }
      break;

In my case, db_affected_rows() returns zero when no change results from the update query (i.e. when re-saving an existing node with no change to the 'private' flag). Thus, the insert query is attempted, which then causes the duplicate entry error message. This patch eliminates the error message.

See also this similar discussion: #798626: Cron produces duplicate entry error in search.module.

CommentFileSizeAuthor
#13 private-899860.patch739 bytesAnonymous (not verified)
private-duplicate-entry.patch619 bytesDavid Lesieur

Comments

Anonymous’s picture

Anonymous (not verified) commented

I tried to reproduce this and don't got the error described. The PHP error supression (@) is bad style. It would be good to reproduce this and work in a better solution than just hidding the error. 1/2 cents.

jaxpax’s picture

jaxpax commented

I to get an entry error when trying to save a node that's already been saved. Both from the Privare module as from the UUID module

user warning: Duplicate entry '7' for key 'PRIMARY' query: INSERT INTO private (nid, private) VALUES (7, 1) in /Applications/MAMP/htdocs/example.com/sites/all/modules/jquery_ui/jquery.ui/private-6.x-1.1.tar/private/private.module on line 199.

user warning: Duplicate entry '15' for key 'PRIMARY' query: INSERT INTO uuid_node_revisions (vid, uuid) VALUES (15, 'c680118c-ef45-11df-81c1-91a27b8013da') in /Applications/MAMP/htdocs/example.com/sites/all/modules/jquery_ui/jquery.ui/uuid-6.x-1.0-beta2.tar/uuid/uuid.module on line 65.
pieterh’s picture

pieterh commented

I just got the following error

user warning: Duplicate entry '8' for key 'PRIMARY' query: INSERT INTO private (nid, private) VALUES (8, 1) in D:\www\tandemclub.nl\www\drupal\sites\all\modules\private\private.module on line 199.

The error is only popping up when i update a private post.

I got this only on a server with my webhosting provider. On my local test/developing server i never got this problem. I started all over with a clean install and still having this error.

_snake_’s picture

_snake_ commented

Subscribe,

Hi, I have the following warning when I'm trying to update a node, but I don't change the private field:

user warning: Duplicate entry '417' for key 'PRIMARY' query: INSERT INTO private (nid, private) VALUES (417, 0) in /eiova/www/sites/all/modules/private/private.module on line 200.

so, looking the code at the private.module file:

    case 'update': // line 197
      db_query('UPDATE {private} SET private = %d WHERE nid = %d', $node->private, $node->nid);
      if (!db_affected_rows()) {
        db_query('INSERT INTO {private} (nid, private) VALUES (%d, %d)', $node->nid, $node->private);
      }
      break;

I think that when I edit the node, but I don't change the private field, and I click "Save", the line 198 is executed, but there isn't any db_affected_rows(), so the insert query is executed too, throwing the warning. Is that correct?

Tomorrow, I try to know if the insert query is executed, using the following line and looking the log file:

error_log(db_affected_rows());

If it's executed, then I think the best way to solve that is making a SELECT query to the private table to know if the nid is there or not, and make later the UPDATE query or the INSERT query respectively.

Thanks.

David Lesieur’s picture

David Lesieur commented

About the PHP error-control operator (@) in my patch: There are a few instances where Drupal core uses it in the exact same fashion (for example, see variable_set()). I agree that it is not an ideal practice, but it avoids adding an extra query.

timb’s picture

timb commented

I am encountering this Duplicate Key error also with Private when saving a previously created node and not changing it's private status.
My set: Ubuntu 10.04: PHP 5.3.2: Drupal 6.20

beatnikdude’s picture

beatnikdude commented

bump
• Drupal 6.20 w/ Private 6.x-1.1
Duplicate Key error when saving an already existing node.

ezra-g’s picture

ezra-g commented
Status: Needs review » Needs work

I can confirm this error with the latest dev release, but simply commenting out this line is not an adequate solution.

ezra-g’s picture

ezra-g commented
Status: Needs work » Reviewed & tested by the community

After reading further into #638702: insertion errors I learned that this is the approach used in the core user.module. This approach suppresses the error for me. I think this is RTBC ;).

timb’s picture

timb commented

Would love to see this committed as opposed to running a patched module

beatnikdude’s picture

beatnikdude commented

lets commit

timb’s picture

timb commented

bump

Anonymous’s picture

Anonymous (not verified) commented
StatusFileSize
new private-899860.patch739 bytes

A year later, a more conservative patch :-)

db_affected_rows() will only return something different than zero if something changed in the row at the UPDATE execution. This can be confirmed with some simple queries in a MySQL client:

mysql> select * from private;
+-----+---------+
| nid | private |
+-----+---------+
|   1 |       0 |
|   2 |       0 |
+-----+---------+
2 rows in set (0.00 sec)

mysql> update private set private = 0 where nid = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update private set private = 1 where nid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update private set private = 0 where nid = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update private set private = 0 where nid = 1;
Query OK, 0 rows affected (0.00 sec)                    << returns "0"
Rows matched: 1  Changed: 0  Warnings: 0
jesusmalaga’s picture

jesusmalaga commented
Issue summary: View changes

There is a much simpler and elegant answer to this. It is probably related to some change in MySQL server behaviour, as we have this module installed in two different servers, and the older one gives no error while the modern is giving the alerts (right alerts, on the other side, as while eriksen noted, there are no update results if status does not change. I have no time now to make a patch, but code change is easy:

============================
.........
case 'insert':
case 'update':
db_query('REPLACE INTO {private} (private,nid) VALUES (%d,%d)', $node->private, $node->nid);
break;
...............
============================
Using this code, as nid is the primary key of table, ensures in a simple sentence that node will be inserted or updated either way

adamps’s picture

adamps commented
Status: Reviewed & tested by the community » Closed (outdated)

Tidy up - close ancient D6 issues. Seems to be fixed in current code using db_merge