By TheFazz on
what i have done

I implemented a multi-site using a single codebase, a shared database and using singlesignon using drupal 4.7.4.

other system info

PHP version: 4.4.4
mySQL: 4.1.21-standard

the problem

I encounter the following errors each time i add contents (nodes)...

* user warning: Duplicate entry '5-5' for key 1 query: INSERT INTO me_node (nid, vid, title, type, uid, status, created, changed, comment, promote, moderate, sticky) VALUES (5, 5, 'test event', 'flexinode-1', 1, 1, 1165932262, 1165932262, 2, 0, 0, 0) in /home/thefazz/public_html/me/includes/database.mysql.inc on line 120.

* user warning: Duplicate entry '5' for key 1 query: INSERT INTO me_node_revisions (nid, vid, title, body, teaser, log, timestamp, uid, format) VALUES (5, 5, 'test event', 'stuff', in /home/thefazz/public_html/me/includes/database.mysql.inc on line 120.

* user warning: Duplicate entry '5' for key 1 query: INSERT INTO me_node_comment_statistics (nid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count) VALUES (5, 1165932262, NULL, 1, 0) in /home/thefazz/public_html/me/includes/database.mysql.inc on line 120.

it would appear that the numbering for each new node addition follows another numbering which duplicates existing node numbers (nid). i think it also affects the 'fid' (files) and 'vid' (views)... this is a major problem. I need help urgently.

the problem occurs for all node type additions (events, blogs, forums, images etc).

this problem started occuring AFTER i combined two site mySQL databases into a shared database to allow for the single sign on. i tested a fresh multi-site & shared mySQL... but this problem did not occur for text additions. however, it seemed to occur for images.

how do i ensure that mySQL remembers the right numbering for each node?

i truly need help here. urgently.

thanks in advance.
fazz

http://www.thefazz.com/me

⋅ Categories: Drupal 4.7.x

Comments

TheFazz’s picture

TheFazz commented

also find that the mySQL tables gives out the following error messages when i do a quick table check:

Problems with indexes of table `me_ef_host`
PRIMARY and INDEX keys should not both be set for column `nid`

Problems with indexes of table `me_ef_register`
phpMyAdmin http://www.thefazz.com:2082/3rdparty/phpMyAdmin/db_deta...
5 of 5 12/12/2006 20:14
PRIMARY and INDEX keys should not both be set for column `nid`

Problems with indexes of table `me_links_node`
More than one UNIQUE key was created for column `nid`

Problems with indexes of table `me_listhandler`
PRIMARY and INDEX keys should not both be set for column `lid`

Problems with indexes of table `me_node`
PRIMARY and INDEX keys should not both be set for column `nid`
More than one INDEX key was created for column `status`

Problems with indexes of table `me_term_hierarchy`
PRIMARY and INDEX keys should not both be set for column `tid`

Problems with indexes of table `me_term_node`
PRIMARY and INDEX keys should not both be set for column `tid`

Problems with indexes of table `me_view_view`
UNIQUE and INDEX keys should not both be set for column `name`

are these critical errors? btw, these errors also occur for fresh installs.

Anonymous’s picture

Anonymous (not verified) commented

Fazz,

Looks like we've been down the same path. I created the same situation by merging the databases for two sites into a single database, using different prefixes to distinguish the tables for the two sites. Then, following the instructions in singlesignon.module, I set up a group of tables to be shared across sites (prefixing these with "shared_").

Here's what I think is happening:

One of the shared tables (sequences) stores the next key to use when adding records to tables. Sequences has two fields: a table name and a key value. Whenever Drupal creates a new record in a table, it checks the sequences table to get the key value to use. If Drupal can't find an entry in sequences for the table being modified, it adds one.

Sure enough, my sequences table had legacy content: most of the table names didn't have prefixes, and the only prefixed entries were for the tables Drupal was trying to modify when it generated the duplicate key errors.

The solution was to clean up the newly shared sequences table by putting in entries for both sites and the shared tables. This meant going back to my backup copies for my two sites, getting the table names and keys for each site, prefixing the table names appropriately, and putting all this into a single, merged sequences table. When done, my version of this table had three sets of records:

- table names from my main site (in my case prefixed with "main_") and the next key values from the backup of my main site;

- table names from my second site (in my case prefixed with "groups_") and the next key values from the backup of my second site;

- table names for any of the shared tables that are listed in singlesignon.module (in my case prefixed with "shared_"), and the appropriate next key values.

That seems to have fixed the problem.

You also mentioned errors about indexes that cropped up when you checked the tables. I'm ignoring those, simply because they seem to be inherent in the sql that creates those tables at install time, and thus common to all drupal installations. Must be there for compatibility reasons or something.

I hope that helps ...

TheFazz’s picture

TheFazz commented

jshuster - thanks. i think this was the problem. i had some help from CogRusty as well. and have done some changes to the sequence table prefixes. so far working ok... although it is not yet properly stress tested.

fazz