This page is for errors similar to the one below:

user error: Duplicate entry '24' for key 1
query: INSERT INTO node (status, moderate, promote, sticky, title, body, format, uid, created, type, teaser, changed, nid) VALUES('1', '0', '1', '0', 'test', 'this is a test', '1', '1', '1149692821', 'story', 'this is a test', '1149692832', '24') in .../includes/database.mysql.inc on line 66.

warning: Cannot modify header information - headers already sent by (output started at .../includes/common.inc:384) in .../includes/common.inc on line 192.

The likely problem (short form): At some point, the sequence table in your Drupal database was not updated.

The solution

  1. Access your database. Check the affected table. In this case, the error tells us that it is the node table (query: INSERT INTO node). This also happens with other tables, modify these instructions accordingly.
  2. This is the node table, so the problem is the node id (nid). Each node has a unique id. Look at your table, and find the highest node id (nid). If you have many nodes, it may help to sort your table by nid to find the highest one.
  3. Go to your sequences table. Change the node id in the sequences table to a number higher than the id you found in step 2.

The problem (long form):

The 'key' in a table is a column where every entry has to be unique. For example, in the users table, every UID (user id number) must be unique. You don't want two users with the same id!

Drupal keeps track of what is the highest ID value separately, in the sequences table. When a new user is created, Drupal checks the sequences table to find out what the next UID should be. Presumably this is more efficient than checking the user table itself. Drupal then creates the new user, updates the user table with the new user information, and updates the sequences table with the new UID. The same thing happens for the node table, comment table, really, a whole bunch of tables.

Sometimes, for some reason, Drupal does not update the sequence table. The number there becomes out of date. So let's say that in the node table the highest NID is 300, but somewhere, something went wrong, and in the sequences table, the NID is only 297. The next time Drupal makes a node, it will check the sequences table, and try to make a node with a NID of 298...whoops! That already exists.

The solution (long form):

The error itself can tell us a lot. Let's divide it into 3 parts:

The error:

user error: Duplicate entry '24' for key 1

Duplicate... meaning the '24' is the same as something else in the table, something where no two things can be the same. Which table? Which column?

The query:

query: INSERT INTO node (status, moderate, promote, sticky, title, body, format, uid, created, type, teaser, changed, nid) 

INSERT INTO [tablename] tells us which table produced the error. What follows is a list of column names.

The values:

VALUES('1', '0', '1', '0', 'test', 'this is a test', '1', '1', '1149692821', 'story', 'this is a test', '1149692832', '24') in .../includes/database.mysql.inc on line 66.

The values we tried to insert into columns of the NODE table... We already know '24' was the problem, we see here it is the LAST value, corresponding to the LAST column, the nid, or node id.

So the problem is, we tried to insert 24 into the nid column of the node table, when that value already existed (duplicate entry!).

Why? Because the sequence table was not updated, and told Drupal the wrong nid. The solution is to enter a value for nid into the sequence table that is higher than ANY nid in the node table.

To find the highest nid in the node table, access your database, however you do it. Find the node table, and sort it by nid. Write down the highest value. Let's say for this example that the highest number is 68.

Go to the sequences table, and find the row that has nid in it. Replace the number there with ANY number higher than the highest value in the node table. For this example, the highest value was 68. 69 would work, so would 70, 90, and 201. ANY NUMBER higher than 68 would work in this example.

Additional point: 99% of the time, the solution is in the sequence table, but this error will come up ANY time you try to insert duplicate data into a column where every value has to be unique.

Comments

bdimaggio’s picture

I have a Drupal 4.6 site that requires hundreds of blocks (don't ask). I was busy putting in the 128th block one day when I got this:

Duplicate entry '127' for key 1 query: INSERT INTO boxes (title, body, info, format) VALUES ('mytitle','mybody','myinfo',2) in /var/www/html/drupal/includes/database.mysql.inc on line 66.

After much consternation, I realized that a) new blocks don't consult the sequences table -- their information goes into the boxes table, getting assigned a new bid through auto_increment, and b) boxes.bid is a signed tinyint, which means that it can't get bigger than 127 -- any value larger than that just gets changed to 127 as it gets passed in. Hence the duplicate key error. I changed the table structure slightly to make bid a smallint (max positive value: 32767), and everything seems to be working again. Meaning, now I can get back to inputting my hundreds of blocks :p

najibx’s picture

In addition to above 3 solutions, also check the "node_revisions" table. Change the node id in the sequences table to a number higher than the id you found in step

-najibx -
Drupal developer in Malaysia

danromanchik’s picture

I was getting this error message when Drupal attempted to write to the watchdog table. I simply did a REPAIR on this table and now it's working just fine again.

jcamfield’s picture

bad settings in pathauto can also create duplicate entries by not providing unique alias information (or providing variables which do not provide unique paths...)

yoyin’s picture

Hello there is a bunch of people who have errors caused by the menu_router table http://drupal.org/node/342193.
I guess that has somethign to do with pathauto?
How do you solved this issue? Or how did you "repair" the concerned table?
thanks & cya

jfro’s picture

I'm running into the same problem here, but in Drupal 6. There is no sessions table in D6.

So... How do I do Step 3 in D6?

Thanks!

Marko B’s picture

I am often getting similar problem with d6. Dont know why, but it happens often. Having lots of moduls and site thats too heavy for hosting so somtimes node creation fails or goes mysql timeout so maybe there are some half inputs or something, not sure. But seems this happens when u have problems like this.

DesignICU’s picture

Also having a lot of SQL timeouts and WARNINGS when trying to insert something into the SYSTEM table — there's no incremental key here to tweak. We're wondering if the issue is having Drupal on a shared hosting platform, and if moving to a dedicated/managed server would resolve theses issues in a superstitious "stand on one leg in a bowl of jelly" sort of way.

bearstar’s picture

In D6, my nid value in the node_counter table was not set to auto-increment.
I changed it to auto-increment and it fixed it for me.