MySQL Bugs: #10855: Duplicate key error in select AFTER RESET QUERY CACHE

Bug #10855	Duplicate key error in select AFTER RESET QUERY CACHE
Submitted:	25 May 2005 8:56	Modified:	26 Jun 2005 6:35
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.4	OS:	Windows (Windows XP sp2)
Assigned to:		CPU Architecture:	Any

Description:
I'm getting this error:

Duplicate entry '...' for key 1

in a SELECT query, this happens ONLY after issuing a RESET QUERY CACHE

Even if the error is identical to bug 10292 I don't think this should be treated as a duplicate, as that error came out after every execution of the query, this one comes out ONLY after a "RESET QUERY CACHE" and the two queries are very different.

How to repeat:
Run this query:

> select m.memblog_id
     , m.blog_name
     , m.member_name
     , m.blog_views
     , m.avatar_path
     , count(e.entry_id) as blog_entries
     , ( select count(c.comment_id)
           from comments c
          where m.memblog_id = c.memblog_id ) as blog_comments
     , max(e.entry_time) as blog_last
  from memblogs m
left outer
  join entries e
    on m.memblog_id = e.memblog_id
 where m.member_status in (1, 2)
group
    by m.memblog_id
     , m.blog_name
     , m.member_name
     , m.blog_views
     , m.avatar_path
order
    by blog_last desc
     , m.blog_views desc
     , m.blog_name
limit 0, 20

it runs normally,
then issue:

>RESET QUERY CACHE

run the query again and you'll get the error:

Duplicate entry '...' for key 1

Could you please write here the tables definition and any sample values of those tables ? I tried to repeat on other tables. Nothing happend.

Just uploaded the file you required
Regards

The file upload size is too small, please tell me how I can send it to you

CREATE TABLE `blogrolls` (
  `blogroll_id` mediumint(8) unsigned NOT NULL auto_increment,
  `linker_id` mediumint(8) unsigned NOT NULL default '0',
  `linkee_id` mediumint(8) unsigned NOT NULL default '0',
  PRIMARY KEY  (`blogroll_id`),
  KEY `ler_id` (`linker_id`),
  KEY `lee_id` (`linkee_id`)
) TYPE=MyISAM AUTO_INCREMENT=272 ;

CREATE TABLE `comments` (
  `comment_id` mediumint(8) unsigned NOT NULL auto_increment,
  `entry_id` mediumint(8) unsigned NOT NULL default '0',
  `memblog_id` mediumint(8) unsigned NOT NULL default '0',
  `comment_text` text,
  `poster_id` mediumint(8) unsigned NOT NULL default '0',
  `comment_time` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`comment_id`),
  KEY `mid` (`memblog_id`),
  KEY `cTime` (`comment_time`),
  KEY `eid` (`entry_id`)
) TYPE=MyISAM AUTO_INCREMENT=4666 ;

CREATE TABLE `entries` (
  `entry_id` mediumint(8) unsigned NOT NULL auto_increment,
  `memblog_id` mediumint(8) unsigned NOT NULL default '0',
  `entry_title` varchar(100) NOT NULL default '',
  `entry_text` text,
  `entry_time` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`entry_id`),
  KEY `eTime` (`entry_time`),
  KEY `mid` (`memblog_id`)
) TYPE=MyISAM AUTO_INCREMENT=2050 ;

CREATE TABLE `memblogs` (
  `memblog_id` mediumint(8) unsigned NOT NULL auto_increment,
  `member_name` varchar(50) NOT NULL default '',
  `blog_name` varchar(100) NOT NULL default '',
  `blog_views` mediumint(8) unsigned NOT NULL default '0',
  `PASSWORD` varchar(50) NOT NULL default '',
  `join_date` int(10) unsigned NOT NULL default '0',
  `member_email` varchar(100) NOT NULL default '',
  `member_status` tinyint(3) unsigned NOT NULL default '0',
  `password_plain` varchar(50) NOT NULL default '',
  `blog_desc` text,
  `time_zone` varchar(8) NOT NULL default '0',
  `comments_headline` varchar(100) NOT NULL default 'comments',
  `avatar_path` varchar(100) NOT NULL default 'NONE',
  PRIMARY KEY  (`memblog_id`),
  KEY `mStatus` (`member_status`)
) TYPE=MyISAM AUTO_INCREMENT=384 ;

I tested with my own values of your tables. Nothing happend. You can zip a file with example values and attach to "Files". Another way you can send me e-mail with your data.

Thanks for dump. Sorry...I was not able to repeat the bug on 5.0.8. Probably the bug was fixed.