Duplicate entry errors in search indexer

search_wipe() is called before doing these inserts.

  # line 567
  search_wipe($sid, $type, TRUE);

  // Insert cleaned up data into dataset
  db_query("INSERT INTO {search_dataset} (sid, type, data, reindex) VALUES (%d, '%s', '%s', %d)", $sid, $type, $accum, 0);

  // Insert results into search index
  foreach ($results[0] as $word => $score) {
    db_query("INSERT INTO {search_index} (word, sid, type, score) VALUES ('%s', %d, '%s', %f)", $word, $sid, $type, $score);
    search_dirty($word);
  }                     

And search_wipe should delete all that stuff:

/**
 * Wipes a part of or the entire search index.
 *
 * @param $sid
 *  (optional) The SID of the item to wipe. If specified, $type must be passed
 *  too.
 * @param $type
 *  (optional) The type of item to wipe.
 */
function search_wipe($sid = NULL, $type = NULL, $reindex = FALSE) {
  if ($type == NULL && $sid == NULL) {
    module_invoke_all('search', 'reset');
  }
  else {
    db_query("DELETE FROM {search_dataset} WHERE sid = %d AND type = '%s'", $sid, $type);
    db_query("DELETE FROM {search_index} WHERE sid = %d AND type = '%s'", $sid, $type);
    // Don't remove links if re-indexing.
    if (!$reindex) {
      db_query("DELETE FROM {search_node_links} WHERE sid = %d AND type = '%s'", $sid, $type);
    }
  }
}

Why isn't it doing that?

Addendum: Also, the array is keyed by words, so those should be unique for a single re-indexing.

I get it: PHP's array keys collate differently, allowing multiple strings that would collide in MySQL's collation.

That's nasty. Is there any way to avoid having to search for each word before adding it? Or would it be simpler to force a binary collation on this column?

Unfortunately I can't reproduce this with a Lorem Ipsum on utf8_general_ci. The search indexer just runs right through without errors. Can you post the collation and specific node text that this is happening with?

Perhaps vardumping the $results array before inserting it would be helpful information, too.

We have a similar issue in D5 which was marked 'fixed'. Not sure if this is a dupe of not. See http://drupal.org/node/143160

Well, the older issue was fixed by adding a unique index on the table. So instead of having duplicate values in the table, we now get errors about trying to insert duplicate values.

Yep. A Drupal 6 install (with search enabled) containing "nevet" and "nevét" in a node (and nothing else) will trigger a:

user warning: Duplicate entry 'nevét-3-node' for key 1 query: INSERT INTO search_index (word, sid, type, score) VALUES ('nevét', 3, 'node', 1) in /web/test/modules/search/search.module on line 574.

on running cron.

So why won't the binary collation work? Is the unique key on word-sid-type is stripping high-bit characters regardless of collation?

Is there any reason someone would want to index accented and unaccented characters separately with search module? Very late in the cycle for this but I'm wondering if something similar to accents module might be an option? Does varchar to binary have performance/space implications here?

What happens to search if it is used in a non-Latin character set like Japanese or Hebrew?

But for that problem I would indeed suggest stripping out or replacing high-bit characters, and doing the same when processing a search query.

I can also reproduce this error.

responding to #1, search_wipe is working fine. It wipes {search_index} for the sid/type combination, then starts inserting the word/sid/type values found, and if this list included the example text "nevet" and "nevét", the first word gets inserted, and the word creates the duplicate error.

So to summarize the above, php recognizes a difference between these two words, but mysql does not.

How can PHP ensure uniqueness without messing up non-Latin alphabets?

While the root cause is a tricky one to solve properly, I know a trivial solution to the error problem:

  foreach ($results[0] as $word => $score) {
    if ((float)db_result(db_query("SELECT score FROM {search_index} WHERE word = '%s' AND sid = %d AND type = '%s'", $word, $sid, $type)) < $score) {
      db_query("INSERT INTO {search_index} (word, sid, type, score) VALUES ('%s', %d, '%s', %f)", $word, $sid, $type, $score);
      search_dirty($word);
    }
  }

This would choose only the word with the maximum score for each set of duplicates. If this sounds like a good solution, I'll make a patch.

this would slow down the reindexing

I know. :/ That's why I hate to be satisfied with such a primitive solution. But this is one of six criticals still in the queue, and if the next release candidate can downgrade it to a performance issue, that would be an improvement.

fwiw I just did a very rudimentary test with some Japanese text and minimum length one character and didn't run into any issues - looks like it's a bug specific to particular accents and nothing more than that.

Sorry particular accents should've been "accents on roman characters", cafe and café causes the same error with utf8_general_ci for example.

More on this bug/behaviour at the mysql forums, doesn't look all that helpful.

What about a strtr to strip accents before insertion? Non-trivial but probably more performant than #18. However I guess this could really mess things up in some languages.

As mentioned, accents module does this but I've not installed it, and first look at the code was five minutes ago.

Also with many sites, even ones not using locale/content translate, we'll see "nevet" and "nevét" alongside "café" and "cafe", right here for example - so stripping diacritics makes sense to me for core.

Looked at utf8_decode() but of course that completely destroys non-latin characters, so useless.

Ideally this should happen with both indexing and searching - it currently does explicit searches for e and é with different result sets. If we strip when indexing, we have to do the same with searching. Google wasn't my friend for a nice clean function to do this unfortunately.

@Gábor#7: this is clearly a problem related to collations. I ran into it several weeks ago will testing the migration of http://drupalfr.org to Drupal 6, but failed to report it due to severe time constraints.

One way to solve that bug is to set the {search_index}.word column to utf8_bin_ci. I just validated on a test site that it solve the problem.

But, with this would mean that we would differentiate between different versions of a word (accented/not accented, etc.).

@catch#25: In fact, collation is not an enemy, it should be our friend. The implementation of the collation is a difficult work, and moreover language-specific. The one-size-fits-all 'utf8_general_ci' is not optimal, but should works well for most latin based languages. Doing a language specific collation and steeming should be in our work plan for D7.

In the meanwhile, because scores are additive, I suggest we do that:

    // The database will collate similar words (accented and non-accented forms, etc.),
    // and the score is additive, so first add and then insert.
    db_query("UPDATE {search_index} SET score = score + %d WHERE word = '%s' AND sid = '%d' AND type = '%s'", $score, $word, $sid, $type);
    if (!db_affected_rows()) {
      db_query("INSERT INTO {search_index} (word, sid, type, score) VALUES ('%s', %d, '%s', %f)", $word, $sid, $type, $score);
    }

Enclosed patch does exactly that.

Applies, fixes the log warnings. Re-indexing a node containing "café cafe" will log errors without the patch, but not afterward.

A second review would be good.

Patch removes the error. If I have two nodes like this:

1. café cafe
2. cafe

Searching for either café or cafe gives me the same result. However, the highlighting is still dependent on the specific string used.

This seems ok to me really, might even be a feature since it'd show results with lazy typing, but still differentiate in the nevet nevét case on display. Very nice!

Looks like mine was the second review.

@Gábor: It looks like a different issue, perhaps related to the upgrade path. Could you dump the schema of the {search_index} table in your current database?

The default one from my empty D6 installation is:

CREATE TABLE IF NOT EXISTS `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Is yours any different?

Also cannot reproduce on fresh installation. I agree it's probably an update path problem where the unique key has to be reset to the full length of the field.

Here's the key-adding code in system.install.

function system_update_6036() {
   ... // if mysql:
      $ret[] = update_sql("ALTER IGNORE TABLE {search_index} ADD UNIQUE KEY word_sid_type (word, sid, type)");
   ... // otherwise
      db_add_unique_key($ret, 'search_index', 'word_sid_type', array('word', 'sid', 'type'));

I was unable to find any relevant update on search_index that changed the length of the word field. So the cause is not yet identified.

Edit: Just for reference, my table structure is identical:

CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

I can't reproduce this key length issue either on a clean install.

CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `score` float default NULL,
  UNIQUE KEY `word_sid_type` (`word`,`sid`,`type`),
  KEY `sid_type` (`sid`,`type`),
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

My live D5 database which has been around since 4.5 also doesn't have any explicit key lengths - just checked in case there might be some sticking around:

CREATE TABLE `search_index` (
  `word` varchar(50) NOT NULL default '',
  `sid` int(10) unsigned NOT NULL default '0',
  `type` varchar(16) default NULL,
  `fromsid` int(10) unsigned NOT NULL default '0',
  `fromtype` varchar(16) default NULL,
  `score` float default NULL,
  KEY `sid_type` (`sid`,`type`),
  KEY `from_sid_type` (`fromsid`,`fromtype`),
  KEY `word` (`word`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |

If Gabor's post in #11 is current, the only difference I see is the line:

`type` varchar(16) default '',

Didn't notice that. Well, with VARCHAR(50) and no explicit key length, I have no idea why the key gets truncated to 10 characters. Weird.

This is still missing for 7.x.

I've committed this to CVS HEAD. Thanks guys.

I am seeing this problems with my 6.2 install. Has the fix that is discussed in this posting been applied to the 6.2 core?

This patch went in before the release of Drupal 6.0. Please open another issue with a detailed description of your problems.

...Need to open this ticket again because I don't believe this has been fixed. I have a new Drupal 6.2 install, and all was running fine for the past week. However, since a few days ago I'm getting numerous errors in my log, dozens of them, all logged within the same minute:

Duplicate entry 'personal-13-node' for key 1 query: INSERT INTO search_index (word, sid, type, score) VALUES ('personal', 13, 'node', 0.949597315436) in /home/bcims/public_html/modules/search/search.module on line 579.

I could definitely use some help in solving this. I was able to construct the template for Drupal 6.2 and am a novice at php and mysql. I can do some rudimentary work on the error but some assistance from more experienced programmers would be very appreciated.

There is a fellow who wrote this article on the problem: http://cmsreport.com/node/1189

RajP. Please open a new issue rather than re-opening this old one. It's likely your duplicate is caused by a slightly different version of the bug so will need to be fixed in a different issue. A quick fix for this is to truncate your search tables and reindex (you can do this via the ui by disabling and uninstalling search module then re-enabling it).

Is this one:

Duplicate entry '1107-node' for key 1 query: INSERT INTO search_dataset (sid, type, data, reindex) VALUES (1107, 'node', ' monter poszycia kadłubów samolotów zagranica referencja job1107 ocena oceń kiepsko ok dobra super extra twoja ocena brak ', 0) w pliku /home/sites/co.uk/public_html/modules/search/search.module, linia 571.

Is related to this issue?

  // Insert cleaned up data into dataset
  db_query("INSERT INTO {search_dataset} (sid, type, data, reindex) VALUES (%d, '%s', '%s', %d)", $sid, $type, $accum, 0);

Duplicates:
#143160: search_index has duplicate (sid, word, type, fromsid=0) entries
#143160: search_index has duplicate (sid, word, type, fromsid=0) entries
http://drupal.org/node/180032

#218403: Duplicate entry errors in search indexer