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

[9 Dec 2006 4:09] Vlad Kudelin
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.
[9 Dec 2006 4:11] Vlad Kudelin
... 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...:).
[9 Dec 2006 11:48] Victoria Reznichenko
Hello Vlad,

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

Please, try the latest 4.1 version.
[9 Dec 2006 17:17] Vlad Kudelin
Thanks, Viktoria, that helps.
How do I close the bug?..
[10 Dec 2006 14:53] Hartmut Holzgraefe
14980