MySQL Bugs: #24869: Mysql Administrator shows bad nuber of rows in a tables

Bug #24869	Mysql Administrator shows bad nuber of rows in a tables
Submitted:	7 Dec 2006 1:43	Modified:	11 Feb 2009 11:06
Reporter:	Luis G. Epifanio Tula	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Administrator	Severity:	S4 (Feature request)
Version:	1.2.4 rc	OS:	Windows (Windows 2003 Server)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	administrator, catalogs, innodb, MySQL, number, of, rows, Schema Tables

Description:
The number of rows indicated by Mysql Administrator 1.2.4 rc is no t the same that indicates the query againts de table( SELECT COUNT(*) from `postings`), every time i push the refresh button the number indicated is not the same every time
Mysql Administrator==>catalogs==>Schema Tables==>Refresh
And theres is not transaction running against the server, only the administrator
The number of rows with tables with a few rows don't change with the refresh button, 
The table cotains 98000 rows stored (this is the value of a select count(*) query), in the Mysql Administrator the difference is not minor (secuence:98316,92713,104435,93160,103747,106463, hey they are Randoms numbers! )
NOTE: the number of rows in the select count(*) no varies (98000 rows)

How to repeat:
Pressing the Refresh Button in
Mysql Administrator==>catalogs==>Schema Tables==>Refresh Button
you can change the number of rows indicated in tables every time you press it

Suggested fix:
I dont have time to see the sources of the mysql Administrator 1.2.4 rc sorry for my English

[b]Description:[/b]
The number of rows indicated by Mysql Administrator 1.2.12 is not the same that
indicates the count() query against the table. When I click the refresh button, the number variates every time..

The table contains 24000 rows, and no queries where executing at the time of the test. The number of rows in the select count(*) did not vary, and showed perfect numbers. Also, the number on my two table (document and tag) differ in the Administrator, although in the SQL Query browser they are perfectly the same..

NOTE: This bug occurs only for my InnoDB Tables, the MyISAM table displays perfectly!

Result set for SQL 'testing' query:

mysql> SELECT (SELECT COUNT(DISTINCT id) FROM document) as documents, (SELECT COUNT(DISTINCT id) FROM tag) as tags;
+-----------+-------+
| documents | tags  |
+-----------+-------+
|     24319 | 24319 |
+-----------+-------+
1 row in set (0.01 sec)

Though the administrator showed: 24319 and 24200, and the tags one differs ~400 records some times..
The tag table is InnoDB, and the document table is MyISAM, only the tag table shows this erroneous behavior..

[b]How to repeat:[/b]
Pressing the Refresh Button in
MySql Administrator==>catalogs==>Schema Tables==>Refresh Button
you can change the number of rows indicated in tables every time you press it

[b]Suggested fix:[/b]
Use normal SQL count() query instead of using the current way of calculating number of rows ?.

[b]Addional Information:[/b]
MySQL Community Edition
Windows XP SP2
Table Engine: InnoDB

Many thanks for writing a feature request. Please try newer version of MySQL Administrator and let us know if all works like you expect it. The newest version is MySQL Administrator 1.2.15

This behavior is by design and results from a limitation of the InnoDB engine. Please read here: http://dev.mysql.com/doc/refman/5.1/en/innodb-restrictions.html.

"SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization."

MA uses SHOW TABLE STATUS for getting all the info about a table. It would be much too slow if we'd do a select count(*) too.