Here's my problem, I imported alot of data from postnuke when we started using drupal. All the nodes came in with nid's of 1000 and up. Everything worked just fine until recently when we got to a thousand nodes, at which point the site locked up and quit accepting new content. To make matters worse I have a problem with the node table now as well (might be related, but it certainly might not). Anyhow browsing about with phpmyadmin shows these two errors on the node table:
PRIMARY and INDEX keys should not both be set for column `nid`
More than one INDEX key was created for column `status`
Do I need to change something in node_counter? I've got plenty of backups going back for months, so I don't mind breaking some things, but I'm unsure where to start kludging.
I'll paste the script I used for importing here. It is modified code from another post somewhere here on drupal, I wish I'd documented it all better, but I'm just lucky I could find this script still lingering about on my server.
# Story Migration
####
INSERT INTO newDrupalInstallDB.node (nid, vid, type, title, uid, status, created, comment, promote, moderate, changed)
SELECT pn_sid+1000,
pn_sid+1000,
"story",
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
2,
1,
unix_timestamp(pn_time),
0,
1,
0,
unix_timestamp(pn_time)
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor1';
INSERT INTO newDrupalInstallDB.node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format)
SELECT pn_sid+1000,
pn_sid+1000,
2,
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
CASE WHEN NOT strcmp(LEFT(pn_hometext,3),"_TP")
THEN substring(pn_hometext,4)
ELSE pn_hometext
END,
CONCAT(pn_hometext,
IF(
strcmp(
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END,""),
CONCAT(
"
",
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END),"")
),
'',
unix_timestamp(pn_time),
3
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor1';
INSERT INTO newDrupalInstallDB.node (nid, vid, type, title, uid, status, created, comment, promote, moderate, changed)
SELECT pn_sid+1000,
pn_sid+1000,
"story",
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
1,
1,
unix_timestamp(pn_time),
0,
1,
0,
unix_timestamp(pn_time)
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor2';
INSERT INTO newDrupalInstallDB.node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format)
SELECT pn_sid+1000,
pn_sid+1000,
1,
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
CASE WHEN NOT strcmp(LEFT(pn_hometext,3),"_TP")
THEN substring(pn_hometext,4)
ELSE pn_hometext
END,
CONCAT(pn_hometext,
IF(
strcmp(
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END,""),
CONCAT(
"
",
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END),"")
),
'',
unix_timestamp(pn_time),
3
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor2';
INSERT INTO newDrupalInstallDB.node (nid, vid, type, title, uid, status, created, comment, promote, moderate, changed)
SELECT pn_sid+1000,
pn_sid+1000,
"story",
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
20,
1,
unix_timestamp(pn_time),
0,
1,
0,
unix_timestamp(pn_time)
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor3';
INSERT INTO newDrupalInstallDB.node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format)
SELECT pn_sid+1000,
pn_sid+1000,
20,
CASE WHEN NOT strcmp(LEFT(pn_title,3),"_TP") THEN substring(pn_title,4) ELSE pn_title END,
CASE WHEN NOT strcmp(LEFT(pn_hometext,3),"_TP")
THEN substring(pn_hometext,4)
ELSE pn_hometext
END,
CONCAT(pn_hometext,
IF(
strcmp(
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END,""),
CONCAT(
"
",
CASE WHEN NOT strcmp(LEFT(pn_bodytext,3),"_TP")
THEN substring(pn_bodytext,4)
ELSE pn_bodytext END),"")
),
'',
unix_timestamp(pn_time),
3
FROM oldpostnukeInstallDB.nuke_stories WHERE pn_informant='oldAuthor3';
Comments
import script
btw - My script is most assuredly a stripped down / slightly modified version of the script from this post:
http://drupal.org/node/9436
so go there, if you stumbled in here looking for a postnuke conversion script
sequences
Your problem is that you need to change an entry in the 'sequences' table. The node_nid value in the sequences table needs to be at least as high as the last node you have in the nodes table. Your import script probably neglected this step.
The phpmyadmin message about indexes and keys is normal and is not a problem.
--Zivtech--
adjust with no luck
I changed the 'node_nid' entry in the 'sequences' table to 2000. But now I get lots of error output with a story submission, like so:
* user warning: Column 'tid' in where clause is ambiguous query: _gsitemap_links SELECT COUNT(*) - 428 FROM term_data LEFT JOIN forum_access fa ON term_data.tid = fa.tid LEFT JOIN acl acl ON acl.name = term_data.tid AND acl.module = 'forum_access' LEFT JOIN acl_user aclu ON aclu.acl_id = acl.acl_id AND aclu.uid = 0 WHERE ((fa.grant_view >= 1 AND fa.rid IN (1)) OR fa.tid IS NULL OR aclu.uid = 0) AND ( tid <= 457) in /var/www/MasterDrupalInstall/drupal-5.12/includes/database.mysql.inc on line 174.
* user warning: Column 'tid' in where clause is ambiguous query: _gsitemap_links SELECT * FROM term_data LEFT JOIN forum_access fa ON term_data.tid = fa.tid LEFT JOIN acl acl ON acl.name = term_data.tid AND acl.module = 'forum_access' LEFT JOIN acl_user aclu ON aclu.acl_id = acl.acl_id AND aclu.uid = 0 WHERE ((fa.grant_view >= 1 AND fa.rid IN (1)) OR fa.tid IS NULL OR aclu.uid = 0) AND ( tid <= 457 ) ORDER BY tid LIMIT 0, 428 in /var/www/MasterDrupalInstall/drupal-5.12/includes/database.mysql.inc on line 174.
Seems to be a problem with tid now. Maybe specifically the term_data_tid in the sequences table? It's set at 457 at the moment. Maybe I should attempt to roll back the database to one of my backups before the problems? My thoughts there are that once the nid's were messed up maybe they rippled fubar throughout the database? Any thoughts or suggestions?
new problem
I dunno.
That looks like an entirely different problem. The sequences fix was one thing, and probably OK now.
This error is a genuine SQL complaint, and looks to be triggered by a combination of contrib modules.
AND ( tid <= 457 )is actually wrong there, and nothing to do with the state of your database. It's a combination of Access Control rewrites and a slightly naive contrib module that didn't anticipate ACL getting involved. Possibly a bug against gsitemap?.dan.
if you are asking a question you think should be documented, please provide a link to the handbook where you think the answer should be found.
| http://www.coders.co.nz/ |
.dan. is the New Zealand Drupal Developer working on Government Web Standards
The problem is your import script
The problem is your import script attempted to insert nodes directly into the db without using the node api. This caused a disparity in your sequences table. It is that table that is used to create all the unique id's in the system (node id's, term id's, vocabulary id's, etc..)
Now for the bad news. The sequences table is very essential to drupal's system operation. In fact most create operations for all kinds of things reference it and because id's are used as unique keys in tables you get all sorts of errors when the sequences table produces an id that is already in use.
It is not recommended to try to insert nodes directly to the drupal db unless you have a very good working knowledge of the system. At this point it may be possible to get your sequences table straightened out but it is not going to be easy and will most likely take a long time to track down all the id information you will need to fix it.
try this?
True that using the API is always better than straight DB, but it's not THAT hard to straighten out once you've messed up.
... or something :-)
In fact we could probably write a 5-line "repair all sequences" script for such an occasion.
.dan.
if you are asking a question you think should be documented, please provide a link to the handbook where you think the answer should be found.
| http://www.coders.co.nz/ |
.dan. is the New Zealand Drupal Developer working on Government Web Standards
limping along
ya those lines definitely seemed to help, and I imagine a 5-line repair all sequences script would pretty nice to have. I do still have some errors when posting, and the site refuses to update.php, but it's limping along. I think it's time to move up to drupal 6 finally.