Bug #9142 Wrong number of rows indicated for InnoDB tables
Submitted: 12 Mar 2005 11:31 Modified: 12 Mar 2005 14:56
Reporter: Adrian Murariu Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Administrator Severity:S3 (Non-critical)
Version:1.0.19 OS:Windows (Windows 2000 Professional)
Assigned to: CPU Architecture:Any

[12 Mar 2005 11:31] Adrian Murariu 
Description:
On catalog view, each table has a number of rows reported. For InnoDB tables with more than 40.000 rows, succesive Refresh button will show you different number of rows even if no clients are accessing server. Bug observed on my PC, I am the only user.

How to repeat:
Just look at rows number for tables and click several times Refresh button
[12 Mar 2005 14:56] Heikki Tuuri 
Hi!

For InnoDB SHOW TABLE STATUS indeed shows an estimate, calculated from 8 dives into the clustered index tree. This is documented in the manual.

Regards,

Heikki
[2 Mar 2010 9:51] Dmitry Dudintsev 
I use MySQL Admin 1.2.12 (client 5.1.11) with MySQL server 5.1.43 on Windows.
My InnoDB table T1 had 5400000 rows. When I looked at the table (Admin > Catalogs > Schema Tables tab) Admin reported the table had 5400225 rows. After I deleted all the rows from T1 and refreshed the list in Admin I see the table has 1382841 rows and occupies 101.1 MB. 'select count(*) from T1' returns 0. I'd add I use a preallocated InnoDB tablespace file of about 100GB.

This behavior looks like a serious bug to me. Here is how I ran into the bug. I am evaluating MySQL and restarted mysqld after a crash. The server reported it finished the recovery and I went to Admin to see the table statistics just to check the recovery was done correctly. I saw the strange row counts and re-created the tablespace from scratch to make sure I did not run into a bug in mysqld. After I got the same result for the second time google found this bug report.

Suppose I need to see real row counts for my InnoDB database tables. I go to Admin > Catalogs > Schema Tables tab. I see the data I need. Do I have to remember to run Maintenance > Optimise for all tables first?