When cache_set is called for a cache entry which already exists in that very form (including the timestamp), the first UPDATE will change no rows (db_affected_rows() == 0), and the subsequent INSERT will fail because the row (i.e., the primary key) already exists.

This can happen only if calling cache_set with the same parameters twice in one second -- but this can happen. Example: locale_refresh_cache() may be called often from locale() when no rows exist in the translation_* tables (which is a bug by itself). The error message then is:

user error: Duplicate entry 'locale:de' for key 1
query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('locale:de', 'N;', 1111769323, 0, '') in /var/www/drupal-cvs/includes/database.mysql.inc on line 66.

I can think of no obvious general fix, except to add another column to cache that is updated with a random number, or to use a SELECT to check for the existence of the row. The function locale_refresh_cache in locale.module seems to have a bug too (separate post).

CommentFileSizeAuthor
#10 cache_set_2.patch1.06 KBchx
#8 cache_set_1.patch769 byteschx
#7 cache_set_0.patch2.16 KBchx
#6 cache_set.patch2.21 KBchx
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Jeremy’s picture

Jeremy CreditAttribution: Jeremy commented

Why is this a problem? If the cache entry is identical, why do you want two copies of it? It seems to me the error is expected and can be ignored.

In any case, you can add microseconds to a timestamp if you really need a finer granularity. I used this method with locking.

wiz-1’s picture

wiz-1 CreditAttribution: wiz-1 commented

Well, instead of a well-formed page the user just sees a blank page with this error message - not an acceptable result, is it? Of course I don't want two identical cache entries...

Jeremy’s picture

Jeremy CreditAttribution: Jeremy commented

Ah, I see. Hmm, that shouldn't be a fatal error. What if you try going to "administer >> settings" and in the "Error handling" section set "Error reporting" to "Write errors to the log"?

wiz-1’s picture

wiz-1 CreditAttribution: wiz-1 commented

I have error logging and display enabled, and usually SQL query errors are reported nicely. I searched my log and found one such entry, but not the one I posted - this error happened more than once for me.

The error results from trigger_error() in the function database.mysql.inc:_db_query(), just as it should -- but obviously this can happen before set_error_handler is called in common.inc. The default error handler just prints the message and terminates the script. I tried to find out where this could happen but wasn't successful.

lestat@www.winged.it’s picture

lestat@www.winged.it CreditAttribution: lestat@www.winged.it commented

I experienced the same issue on 4.6.0, with Italian locale - some strings were translated by me.
I solved with a small patch that adds a SELECT before the UPDATE.
Until someone tells me a better solution... :)

Check out at http://www.winged.it/node/136

Feedback is greatly appreciated!

chx’s picture

chx CreditAttribution: chx commented
Assigned: Unassigned » chx
Priority: Normal » Critical
FileSize
cache_set.patch2.21 KB

We can work around this mysql bug easily. Note that no surplus queries are executed.

chx’s picture

chx CreditAttribution: chx commented
FileSize
cache_set_0.patch2.16 KB

Reworded the help, 'cos db_matched can return less rows than matched, 'cos it first tries mysql_affected_rows.

chx’s picture

chx CreditAttribution: chx commented
FileSize
cache_set_1.patch769 bytes

You can read about mysql connect flags on the mysql manual page of mysql_real_connect():

CLIENT_FOUND_ROWS Return the number of found (matched) rows, not the number of affected rows.

Ah! That's what we really need. PHP does not define this, but the value of the above constant is 2.

chx’s picture

chx CreditAttribution: chx commented

And note that client_flags is only supported since PHP 4.3.0.

chx’s picture

chx CreditAttribution: chx commented
FileSize
cache_set_2.patch1.06 KB

Drumm asked me to move doc into code. He is right.

Dries’s picture

Dries CreditAttribution: Dries commented

Committed to HEAD. Thanks chx.

Anonymous’s picture

(not verified) CreditAttribution: commented
filiptc’s picture

filiptc CreditAttribution: filiptc commented
Version: » 4.6.5

Version 4.6.5 and I'm having that same issue. I haven't managed to apply the .patch files automatically (WindowsXP) so I've tried manually but the some of the files they apply to seem to be a newer version and code replacement made drupal not work (sites are blank).

What do I do to make this work? Thanks in advance!
Phil

expl0it’s picture

expl0it CreditAttribution: expl0it commented
Status: Closed (fixed) » Closed (won't fix)

Duplicate entry 'locale:en' for key 1 query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('locale:en', 'N;', 1143742202, 0, '') in /home/projectw/public_html/includes/database.mysql.inc on line 66.

i m also getting this mesage at my site and unable to figure out what to do so plz show me the right way how to fix the problem