Bug #63869 different table_rows of a same table

• Submitted: 29 Dec 2011 2:44
• Modified: 29 Dec 2011 5:47
• Reporter: jia xiaolei
• Status: Not a Bug
• Category: MySQL Server: Command-line Clients
• Severity: S3 (Non-critical)
• Version: 5.1.41-3ubuntu12.10-log (Ubuntu)
• OS: Any
• CPU Architecture: Any

[29 Dec 2011 2:44] jia xiaolei

Description:
hi, all:

Most of time, if you want to get the records of a table, 'select count(*) from [table_name]' is okay.
While, I find use " select   TABLE_NAME, PARTITION_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH from  INFORMATION_SCHEMA.PARTITIONS  where TABLE_SCHEMA = 'LN_PABB2' and TABLE_NAME='T_BOUND' ;" get a different table_rows. 
Is anyone encounter with the problem? 

#NOTE: the results using different sql:

+---------------------------+----------------+------------+----------------+-------------+
| TABLE_NAME                | PARTITION_NAME | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH |
+---------------------------+----------------+------------+----------------+-------------+
| T_ADMINISTRATOR           | NULL           |          0 |              0 |       16384 |
| T_ADMINISTRATOR_LOGIN_LOG | NULL           |          0 |              0 |       16384 |
| T_ADMINISTRATOR_SOURCE    | NULL           |          5 |           3276 |       16384 |
| T_AREA_PRIVILEGE          | NULL           |          0 |              0 |       16384 |
| T_BOUND                   | NULL           |    8182737 |             95 |   781189120 |
| T_BOUND_DATA              | NULL           |   16365429 |             56 |   918552576 |
| T_BOUND_EVENT             | NULL           |   10144037 |             86 |   880820224 |
...

mysql> select count(id) from T_BOUND_EVENT ;
+-----------+
| count(id) |
+-----------+
|  10000001 |
+-----------+
1 row in set (10.05 sec)

mysql> select count(id) from T_BOUND_EVENT ;
+-----------+
| count(id) |
+-----------+
|  10000001 |
+-----------+
1 row in set (10.05 sec)

mysql> select count(id) from T_BOUND ;
+-----------+
| count(id) |
+-----------+
|   8182546 |
+-----------+
1 row in set (6.26 sec)

mysql> select count(*) from T_BOUND ;
+----------+
| count(*) |
+----------+
|  8182546 |
+----------+
1 row in set (1.82 sec)

mysql> select count(*) from T_BOUND ;
+----------+
| count(*) |
+----------+
|  8182546 |
+----------+
1 row in set (0.00 sec)

How to repeat:
repeat it is so easy.
count the table_rows using "select count(*)" and "INFORMATION_SCHEMA.PARTITIONS"

Suggested fix:
I do not know the operating on detail. On my experience, it may be the fault of "INFORMATION_SCHEMA.PARTITIONS"

[29 Dec 2011 4:12] Valeriy Kravchuk

Do you use InnoDB tables? 

If you do, this is not a bug. Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/partitions-table.html:

"For partitioned InnoDB tables, the row count given in the TABLE_ROWS column is only an estimated value used in SQL optimization, and may not always be exact."

[29 Dec 2011 5:45] jia xiaolei

Sorry and thanks!
I use mysql-partiton indeed, and I read the manual in "http://dev.mysql.com/doc/refman/5.5/en/partitions-table.html"。

okay, now the "bug" is solved and all can ignore it!

Apologize for my witless again!

-- Jia Xiaolei