MySQL Bugs: #24931: count(*) messed up

Bug #24931	count(*) messed up
Submitted:	9 Dec 2006 4:09	Modified:	10 Dec 2006 14:53
Reporter:	Vlad Kudelin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	14.7 Distrib 4.1.14, for pc-l	OS:	Linux (RH Linux)
Assigned to:		CPU Architecture:	Any

Description:
I by accident have found out that count(*) on SOME tables, for SOME specific queries, reports total garbage.

At first, I didn't believe my eyes.
Here is the manifestation:

mysql> select  the_id,count(*) from T where the_id in (966,1151) group by the_id;
+-----------+----------+
|    the_id | count(*) |
+-----------+----------+
|       966 |    50468 |
|      1151 |    27152 |
+-----------+----------+
2 rows in set (1.12 sec)

mysql> select  the_id,count(*) from T where the_id in (966) group by the_id;
+-----------+----------+
|    the_id | count(*) |
+-----------+----------+
|       966 |        2 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> Bye

myisamchk with all options from -r to -o didn't fix this.

I am right now rebuilding indexes ith the hope that will help.

But in general: pretty scary, one cannot rely on anything.

Please tell: how to detect such problems and repair.

How to repeat:
It is in description.

Suggested fix:
That would be cool if I could suggest a fix.

Well... I guess counts are cached somewhere, so having an option like "myisamchk -r -what=messed_up_chache" would do in my case.

... Sorry, forgot to supply the simplest form (maybe to make it clearer):

mysql> select count(*) from T where the_id=966;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

// And I *know* that it's not 2...:).

Hello Vlad,

Your bug report looks similar to:
http://bugs.mysql.com/bug.php?id=14980

Please, try the latest 4.1 version.

Thanks, Viktoria, that helps.
How do I close the bug?..