Bug #29374 Row count not computed correctly with TEXT/BLOB data types

• Submitted: 26 Jun 2007 20:45
• Modified: 26 Jun 2007 21:59
• Reporter: Stephen Ash
• Status: Not a Bug
• Category: MySQL Administrator
• Severity: S3 (Non-critical)
• Version: 1.2.12
• OS: Any
• CPU Architecture: Any
• Tags: BLOB, data_types, rows, text

[26 Jun 2007 20:45] Stephen Ash

Description:
When a table is created using any of the *TEXT or *BLOB (where * != TINY), the row count in MySQL Administrator catalog view is incorrect.  The value of the TEXT field must fill the entire field for the computation to fail.

How to repeat:
See attached SQL file.

MySQL Administrator reports 36 rows while there are only 6 present.

[26 Jun 2007 20:46] Stephen Ash

SQL Dump of example table

Attachment: mysql_bug.sql (application/octet-stream, text), 110.55 KiB.

[26 Jun 2007 21:59] MySQL Verification Team

Thank you for the bug report. This isn't a MySQL Administrator bug instead
it is the documented behavior of InnoDB engine with the command show table
status:

http://dev.mysql.com/doc/refman/5.0/en/show-table-status.html

Rows 
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count. 

below the result using the client command tool:

c:\dev\5.0>bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.46-nt Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show table status\G
*************************** 1. row ***************************
           Name: eventlog
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 36
 Avg_row_length: 3640
    Data_length: 131072
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 7
    Create_time: 2007-06-26 18:44:48
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment: InnoDB free: 4096 kB
1 row in set (0.00 sec)

mysql>