[drupal-devel] [bug] Duplicate entry 'locale:pl' for key 1 query: INSERT INTO cache

Thu Aug 11 14:23:46 UTC 2005

Issue status update for 
http://drupal.org/node/21568
Post a follow up: 
http://drupal.org/project/comments/add/21568

 Project:      Drupal
 Version:      4.5.2
 Component:    database system
 Category:     bug reports
 Priority:     normal
 Assigned to:  Anonymous
 Reported by:  Cvbge
 Updated by:   katin
 Status:       patch (code needs review)

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.

katin

Previous comments:
------------------------------------------------------------------------

Fri, 29 Apr 2005 07:01:29 +0000 : Cvbge

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.

------------------------------------------------------------------------

Sun, 05 Jun 2005 06:34:59 +0000 : chx

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

------------------------------------------------------------------------

Fri, 29 Jul 2005 05:42:57 +0000 : bslade

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

Ben in DC
PublicMailbox at benslade dot com
[1] http://drupal.org/node/1811

------------------------------------------------------------------------

Thu, 11 Aug 2005 12:25:01 +0000 : katin

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).