MySQL Bugs: #20006: Number of Rows is not Accurate for MyISAM

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:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (Redhat Linux)
Assigned to:		CPU Architecture:	Any

Description:
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:

while(1){
  show table status;
  sleep 180;
}

in another thread:

update or select the same table, but no insert/delete.

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.

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?

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

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.

Please, reopen this bug report when you'll have results. Run CHECK TABLE on that table on master in case of different counts.

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