Bug #20006 Number of Rows is not Accurate for MyISAM
Submitted: 22 May 2006 17:56 Modified: 23 Jun 2006 14:58
Reporter: Xin Li Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.19 OS:Linux (Redhat Linux)
Assigned to: CPU Architecture:Any

[22 May 2006 17:56] Xin Li
When I setup replication, I also have a script to monitor it. The main part of the monitoring script is, to use "show table status" on both of  master and slave to compare number of rows for each table. 

However, I noticed sometimes it doesn't work.

at 9:42, I got
main::compare_table_rows:Master.bugs.bugs: 123974 rows
main::compare_table_rows:Slave.bugs.bugs: 123974 rows
at 9:45 I got
main::compare_table_rows:Master.bugs.bugs: 123963 rows
main::compare_table_rows:Slave.bugs.bugs: 123974 rows
at 9:48 I got
main::compare_table_rows:Master.bugs.bugs: 123974 rows
main::compare_table_rows:Slave.bugs.bugs: 123974 rows
So, you can see nothing changed in database, but at 9:45, rows in master 
is even 11 less than rows in slave!
I'm wondering how MySQL update "table status". Is it possible the "rows" 
is not accurate at some time? The weird thing is, both at 9:42 and 9:48 seems correct but 9:45.

How to repeat:
in one thread:

  show table status;
  sleep 180;

in another thread:

update or select the same table, but no insert/delete.
[23 May 2006 7:05] Valeriy Kravchuk
Thank you for a problem report. If table is MyISAM, and no other statements like DELETE and then INSERT (REPLACE) are perfromed, then it is a bug. So, please, try to repeat with a newer version, 5.0.21, and add SELECT COUNT(*) FROM bugs.bugs to you monitoring script. Let's compare both results.
[23 May 2006 13:11] Xin Li
I believe this is a bug, because

when I checked the following logging info:

main::compare_status: Slave: Slave_SQL_Running: Yes
main::compare_status: Slave: Slave_IO_Running: Yes
main::compare_status: Slave: Last_Error: 
main::compare_status: Slave: Last_Errno: 0
main::compare_status: Slave: IO State: Waiting for master to send event
main::compare_status:Master File No: 000753
main::compare_status:Slave  File No: 000753
main::compare_status: Master: mysqld-bin.000753:51379807
main::compare_status: Slave(exec):  mysqld-bin.000753:51379807
main::compare_status: Slave(read):  mysqld-bin.000753:51379807

I can see no error for replication, and slave read/exec position is the same as master's. Therefore, the number of rows in master should be the same as in slave.

I didn't try select count(*). Does it take more time?
[23 May 2006 14:40] Valeriy Kravchuk
> I didn't try select count(*). Does it take more time?

No, I do not think so. It should run fast(er) for MyISAM table with a primary key. Plese, check. I just want to isolate the bug, if any.
[23 May 2006 14:53] Xin Li
OK. I will add select count(*) as well. Because the behaviour happens once a day or once a few days, it may take some time I can report the result here. Thank you for taking care of this issue.
[23 May 2006 14:58] Valeriy Kravchuk
Please, reopen this bug report when you'll have results. Run CHECK TABLE on that table on master in case of different counts.
[23 Jun 2006 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".