By bennybobw on

Recently I modified the wp_migrate script to work with Drupal 4.7 and the a site I had on B2Evo. Everything seemed to be fine, until today when I tried to add a new node from the Drupal backend, I received this error.

Warning: Duplicate entry '1' for key 2 query: INSERT INTO node (nid, vid, title, type, uid, status, created, changed, comment, promote, moderate, sticky) VALUES (85, 1, 'Passion and Back Update', 'story', 2, 1, 1163609642, 1163609642, 2, 1, 0, 0) in /home/bennybobw/workspace/drupal47/drupal/includes/database.mysql.inc on line 121

I also get an error for node_revisions. According to the api documentation on node_save(), Drupal should be getting a unique id for the vid field in the node table (there are 85 posts in the database, without revisions, so technically it should be 85). Does anyone know how to fix this? Thanks.

-bennybobw

⋅ Categories: Drupal 4.7.x

Comments

bennybobw’s picture

bennybobw commented

I've also tried this in Drupal Head and I'm getting this same error.

I tried changing the 'vid' column to auto_increment but that didn't change anything. In node, the max nid = 84, which is correct. It is pulling a unique value. In node_revisions, max vid = 84, but Drupal is trying to use 1 when it sets up the new node. What am I missing here? Why isn't Drupal generating a unique vid?

-bennybobw

allella’s picture

allella commented

I received a similar Warning: Duplicate entry while using the Content Management -> Image Import feature for Drupal 5. This was likely the result of my earlier messing with the {node} table's autoindex value in MySQL while manually importing nodes through phpMyAdmin.

Anyway, I wondered the same thing about why Drupal was ignoring the {node}.nid autoindex. As the initial post points out, the node_save() function generates the next number. That function then calls db_next_id() to get the next values for {node}.nid and {node_revisions}.vid . Upon looking at the code for db_next_id

function db_next_id($name) {
  $id = db_result(db_query("SELECT nextval('%s_seq')", db_prefix_tables($name)));
  return $id;
}

I noticed the nextval function, which is related to MySQL sequences. I still don't get how sequences work, or why they are necessary, but when I looked in the Drupal MySQL tables I quickly figured out the reason for the {sequences} table.

I used phpMyAdmin to browse this {sequences} table and found rows where the name field was set to "node_nid" and "node_revisions_vid", which explained where that nexval() got the next node number. The corresponding {sequences}.id field in the row apparently relates to the last node saved. In my case, both the "node_nid" row and "node_revisions_vid" row were incorrectly set to 202.

Using phpMyAdmin, I looked at the Structure tab of the {node} table and found the correct Next Autoindex value to be 247. {node_revisions} doesn't have an index, so I just assumed its sequence value should be the same as {node}.

Long story short, I used phpMyAdmin to set the {sequences}.id value for the "node_nid" and "node_revisions_vid" rows equal to the index of the last node created (not the next node to be created). This happens to be the {node} table's Autoindex minus 1, or in my case 246.

It should also be noted that I manually cleared out the partial node that was created by the bad Image Import. I ended up removing rows related to node 202 from {url_alias} (not necessary if you don't use this module), {files}, {files_revisions}, {node_revisions}. I also deleted the image files generated in the import process (on my server this was /files/images/). I moved the original .jpg file back into my import folder /files/images/import. After fixing the sequence table and clearing up the fragmented node 202 the second attempt to import the image with Image Import worked fine.

Hooray. Hope this saves someone some time.

Jim

OrangeCoat.com