[documentation] [Documentation feature] page on sequences table (duplicate entry errors) in troubleshooting faq

[rivena]

Issue status update for 
http://drupal.org/node/64057
Post a follow up: 
http://drupal.org/project/comments/add/64057

 Project:      Documentation
 Version:      <none>
 Component:    Installation
 Category:     feature requests
 Priority:     normal
 Assigned to:  rivena
 Reported by:  rivena
 Updated by:   rivena
-Status:       patch (code needs work)
+Status:       fixed

I have submitted the page to the handbook, it's currently in the
moderation que.


Anisa.




rivena



Previous comments:
------------------------------------------------------------------------

Wed, 17 May 2006 13:42:26 +0000 : rivena

I think it would be a *fab* idea to have a page on this in the trouble
shooting faq.


I will write up a page; if you all know of some forum topics that
reference this problem (same problem, so many different errors!),
please post them with this issue.


Anisa.




------------------------------------------------------------------------

Wed, 07 Jun 2006 18:44:23 +0000 : rivena

Here is my rough draft!  I removed the formatting so you can see it in
your email.


In addition, I think new links/info on the following would be good:
HOWTO: Access your database
HOWTO: Edit your database
Other forum examples of errors.
Explanation for why this happens.


-------------------------------
PLAIN TEXT VERSION


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 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 nodetable (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. 


Voila!
----------------------------------


I would call this 'Duplicate entry error' and put this in the
Troubleshooting FAQ.  Please let me know what you think.


Anisa.




------------------------------------------------------------------------

Sun, 11 Jun 2006 20:13:10 +0000 : rivena

If this is OK as is, I will submit it to the handbook as a page.... ?


Anisa.




------------------------------------------------------------------------

Mon, 12 Jun 2006 21:27:43 +0000 : PAAHCweb

Bless you for posting this!  Couldn't find anything helpful through
Drupal search.  The host server went down while I was editing my site. 
Duplicate entry errors appear when I try to access settings menu. 


I'm having trouble applying this to my situation, and it seems unclear
what you mean by "a number higher than the id you find there."   Does
that mean (1) higher?


To illustrate, my error is related to the system table.  The only
integer values there are "status" and "bootstrap".  This is an
abbreviated entry for "status":



id  	select_type  	table  		type  	rows  	Extra  
1	SIMPLE		paahc_site_system 	ALL 	95 	Using temporary; Using filesort

The rows are further defined as 
52  |  0
43  |  1

Should I use the value for "rows" to update the sequences table? 
Presently the value for "id" in the sequences table is 10.  So would
that 106?  96?  or something in another ballpark?


Thanks,
D. Lynn




------------------------------------------------------------------------

Tue, 13 Jun 2006 07:20:05 +0000 : rivena

Sorry to disappoint you, but I think that your problem may be different.


The only things that must have unique values in a table are the indexed
fields.  For your systems table, that's the file name field.  What is
your error exactly?  Do you have two modules with the same name?


With my solution here, the problem is with, for example, node ids
(nid).  Each node must have a unique id.  Each time a node is created,
the node gets an ID that is one higher than the old one, and the value
in the sequences table is increased by 1.   Instead of asking the node
table what the next id is, Drupal looks to the sequences table.  So if
the sequence table isn't updated, Drupal comes up with an old ID, and
creates the error.


The error is because you are trying to create something that has the
same ID (or Name in this case) as something that already exists.


Anisa.




------------------------------------------------------------------------

Wed, 14 Jun 2006 07:40:34 +0000 : PAAHCweb

OK, I understand better, thank you Anisa.  


There was an avalanche(?) of "header already sent", 13 duplicate entry
errors arising from the system table (each referencing a different
module), then "mdir(temp) permission denied".  Since these errors all
happened at the same minute, I assumed they were connected, but maybe
not.  The duplicate errors may have happened because for a while after
the reboot, there were two copies of my site on the server.  Only the
permission denied error persists when I try to access settings.  


I'm not disappointed at all!  Your explanation was very helpful because
the dang MySQL documentation is so hard to understand.  It's an
excellent idea to "translate" some common SQL errors, with simple
fixes.  


Again thanks,
D. Lynn