Duplicate entry 'locale:pl' for key 1 query: INSERT INTO cache [#21568]

Sometimes I'm getting following errors:
(I'm not sure what triggers it, or what's the cause - I suppose it's localization or image module, maybe albums...)

Location /system/files?file=images/zaulek_onanistow.thumbnail.jpg
Wiadomość Duplicate entry 'locale:pl' for key 1 query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('locale:pl', 'a:1652:{s:15:\"Download method\";b:1;s:49:\"Public - files are available using http directly.\";b:1;s:42:\"Private - files are transferred by Drupal.\";b:1;s:20:\"File system settings\";b:1;s:34:\"Select an image processing toolkit\";b:1;s:14:\"Image handling\";b:1;s:23:\"Configurable time zones\";b:1;s:17:\"Short date format\";b:1;s:33:\"The short format of date display.\";b:1;s:18:\"Medium date format\";b:1;s:30:\"The medium sized date display.\";b:1;s:16:\"Long date format\";b:1;s:45:\"Longer date format used for detailed display.\";b:1;s:45:\"The first day of the week for calendar views.\";b:1;s:62:\"Only JPEG, PNG and GIF images are allowed to be used as logos.\";b:1;s:66:\"Check here if you want the theme to use the logo supplied in /home/www-data/izoo/includes/database.mysql.inc on line 66.

Location /system/files?file=images/zaulek_onanistow.thumbnail.jpg
Wiadomość Duplicate entry 'locale:en' for key 1 query: INSERT INTO cache (cid, data, created, expire, headers) VALUES ('locale:en', 'a:1652:{s:15:\"Download method\";b:1;s:49:\"Public - files are available using http directly.\";b:1;s:42:\"Private - files are transferred by Drupal.\";b:1;s:20:\"File system settings\";b:1;s:34:\"Select an image processing toolkit\";b:1;s:14:\"Image handling\";b:1;s:23:\"Configurable time zones\";b:1;s:17:\"Short date format\";b:1;s:33:\"The short format of date display.\";b:1;s:18:\"Medium date format\";b:1;s:30:\"The medium sized date display.\";b:1;s:16:\"Long date format\";b:1;s:45:\"Longer date format used for detailed display.\";b:1;s:45:\"The first day of the week for calendar views.\";b:1;s:62:\"Only JPEG, PNG and GIF images are allowed to be used as logos.\";b:1;s:66:\"Check here if you want the theme to use the logo supplied in /home/www-data/izoo/includes/database.mysql.inc on line 66.

Location /system/files?file=images/zaulek_onanistow.thumbnail.jpg
Wiadomość Cannot modify header information - headers already sent by (output started at /home/www-data/izoo/includes/common.inc:384) in /home/www-data/izoo/includes/file.inc on line 437.

Comment	File	Size	Author
#14	database.mysql.inc_5.patch	527 bytes	magico
#14
#12	database.mysql.inc_4.patch	559 bytes	robertgarrigos
#12

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Comment #1

chx CreditAttribution: chx commented 5 June 2005 at 06:34

This is fixed in HEAD. If you want, you may change the mysql_connect line by adding ,2 after TRUE.

Comment #2

(not verified) CreditAttribution: commented 19 June 2005 at 06:57

Comment #3

bslade CreditAttribution: bslade commented 29 July 2005 at 05:42

Version:

» 4.6.0

Could this related to the issue (bug) "improved cache api - race conditions"? ( http://drupal.org/node/1811 )

Ben in DC
PublicMailbox at benslade dot com

Comment #4

katin CreditAttribution: katin commented 11 August 2005 at 12:25

Version:	4.6.0	» 4.5.2
Status:	Closed (fixed)	» Needs review

I fixed it by changing some logic in bootstrap.inc. The function is cache_set():

(I tried to post the actual code here inside <code> tags, but kept getting 'Terminated request because of suspicious input data.' and all my text would be lost. After a dozen tries moving code tags, trimming lines, etc., I give up. You'll have to go look at the code yourself.)

The logic in this function is:
UPDATE record
if (!db_affected_rows()) then INSERT the record

I found a clue to why this logic would fail in the PHP manual, mysql_affected_rows page:
(http://us2.php.net/manual/en/function.mysql-affected-rows.php)

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

The REPLACE statement first deletes the record with the same primary key and then inserts the new record. This function returns the number of deleted records plus the number of inserted records.

That would mean that if the record specified in the UPDATE line is already in the db exactly as so, mysql_affected_rows() would return zero, and the logic then tries to INSERT the record - which will throw the error because it already exists.

Therefore, change the INSERT to a REPLACE and the logic will work fine for duplicate as well as changed cache entries. The error messages stop.

(Again, tried to post code samples here for the fixed line... couldn't get past the 'suspicious data' errors... I had no disallowed tags... really annoying.)

Background:
MySQL ver 4.1.11
PHP ver 4.3.11
Drupal version 4.6.2
Linux platform

I suspect the exact behaviour of mysql_affected_rows() in relation to the UPDATE function may differ in different versions of mysql (3.23 to 4.1, for example).

Comment #5

katin CreditAttribution: katin commented 11 August 2005 at 14:23

It's going to require more logic changes than that... since things error out on the REPLACE if the record *isn't* in the cache yet. Something like:

If (REPLACE fails) then INSERT

I'll test some code on my sites and post updates.

Comment #6

chx CreditAttribution: chx commented 11 August 2005 at 14:34

Status:

Needs review

» Fixed

This is fixed in HEAD.

Comment #7

Cvbge CreditAttribution: Cvbge commented 11 August 2005 at 14:45

This bug was for 4.5.2, so if it's still present in 4.5 version the bug should be opened.

Comment #8

(not verified) CreditAttribution: commented 25 August 2005 at 15:00

Comment #9

(not verified) CreditAttribution: commented 8 September 2005 at 15:11

Comment #10

(not verified) CreditAttribution: commented 22 September 2005 at 19:42

Comment #11

(not verified) CreditAttribution: commented 6 October 2005 at 20:00

Status:

Fixed

» Closed (fixed)

Comment #12

robertgarrigos CreditAttribution: robertgarrigos commented 12 October 2006 at 10:41

Version:	4.5.2	» 4.6.9
Assigned:	Unassigned	» robertgarrigos
Priority:	Normal	» Critical
Status:	Closed (fixed)	» Reviewed & tested by the community

File	Size
database.mysql.inc_4.patch	559 bytes

This needs still to be fixed with 4.6.9. This was also affecting image.module which cannot show a thumbnail because of the message sent before a new header is added by file_transfer() function, called by image_fetch(). See this bug