By modul on

Hi all,

Usually, my site works without any problem. Every now and then, say once a month or so, I get an error like this:

Duplicate entry 'some_term_from_some_text' for key 1 query: search_update_totals INSERT INTO my_prefix_search_total (word, count) VALUES (' some_term_from_some_text ', 0.23364468391019) in /mysite.com/includes/database.mysql.inc on line 172.

- When I do a search for "some_term_from_some_text", the node in which it occurs is listed twice. That's the only "unwanted result" I can find.
- It always has to do with the my_prefix_search_total table
- When it happens, it happens all at once, i.e. I get a bunch or similar errors - today, for instance, I got maybe 30 or so, all together at the same moment. For the rest of the day, nothing showed up, although new texts were entered afterwards.

I did a good deal of reading on this forum with regard to this duplicate entry thing, but I haven't found anything useful, i.e., very often, the error was due to some bad code in some contributed module, which was then patched. My error seems to be coming from the core. Btw, I'm using 5.7 - ok, I know, I should upgrade... But it doesn't seem to be an error connected to a particular version of Drupal... * sigh *

My questions:

1) How bad is it to have this "duplicate entry" error?
2) Where does it come from? And why does it happen only sometimes?
3) How can I prevent it?
4) How can I get rid of the erroneous results already entered in my database? I.e., how can I go back to just one instance in my search results?
5) Should I panic, or is this something that "just happens"?

Thanks!!

⋅ Categories: Drupal 5.x

Comments

vm’s picture

vm commented

just from a secuirty stand point you should update to the lastest version of 5.x you are wide open for an XSS attack and will be pissed when your DB is erased or some such because you didn't update.

modul’s picture

modul commented

I am grateful to VeryMisunderstood for the wisdom of the reply.
As far as my questions go, is there someone who could give me some hints? How to fix a "duplicate entry" error? How to prevent them? How bad are they?...?

modul’s picture

modul commented

I'm still wondering: How to fix a "duplicate entry" error? How to prevent them? How bad are they?...? Hope there's a kind soul out here willing to help me out on this one.

vm’s picture

vm commented

There isn't much that can be siad here about those errors that you can't learn from mysql.net or google.

any error like that is bad, how bad, I can't say. If you corrupt your database that can be pretty bad.

modul’s picture

modul commented

Thanks, VeryMisunderstood. I did another check on drupal.org, and stumbled across this very old thread: http://drupal.org/node/19887

There is a solution given, which looks appealing, but because it's so darn old, I'm a bit afraid to use it. Could anyone take a look and tell me if I would do something wrong by running this code on my 5.7 search_total table??

delete from search_total;
insert into search_total (word,count) select word,sum(score) from search_index group by word;

If usable, this might correct my table. There still is the issue of preventing this error to occur in the future, though. Any hints on that one?

Thanks guys (and gals) !

vm’s picture

vm commented

The best way to test what you found is to export your database into a test site and try it on that test site. if it works. GRRRRRRRRRRRRRRREAT! if it doesn't someone can try to make it work.

hidehisa’s picture

hidehisa commented

Hi.

As told in http://drupal.org/node/264082 , this error comes up from funcion search_update_totals() in standard search.module.

When updating search_total table, the function do this:
1. try UPDATE {search_total}
2. check if update was successfull using db_rows_affected()
3. if db_rows_affected() returns 1, the row exists and updated, OK
4. if db_rows_affected() returns 0, the row doesn't exist, need to do INSERT {search_total} ...

The problem occurs when
- Row with given key exists
- AND
- Updating SQL doesn't affect actual table (no change occurs, updating to the same values)
db_rows_affected() returns ZERO and search_update_totals() function trys INSERT statement,
and duplicate key error occurs.

SOLUTION:
These errors are just messing watchdog log after each cron, but has no problem, so just ignore them, you don't have to cope with it.

But, if you don't want to see errors, try below (I've not tested but it will work).

in funcion search_update_totals() in search.module

A: change
INSERT INTO {search_total} (word, count) VALUES ('%s', %f)
to
INSERT IGNORE INTO {search_total} (word, count) VALUES ('%s', %f)

OR

B: check for row's existence using SELECT statement to determine which of INSERT or UPDATE to use

I've not read all of search_dirty() related codes in search.module, I think they're doint this because when UPDATE occurs, values have changed for sure. Using SELECT query when not necessary has (very little, but accumulating) negative effect for DB performance, especially on query cache I guess. So just adding IGNORE will do..