MySQL Bugs: #25416: show table status doesn't report rows for falcon table correctly

Bug #25416	show table status doesn't report rows for falcon table correctly
Submitted:	4 Jan 2007 13:46	Modified:	26 May 2010 17:48
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	mysql-5.2.0-falcon-alpha, 5.2.3-falcon-alpha-debug	OS:	Linux (suse9.3 x86)
Assigned to:	Ann Harrison	CPU Architecture:	Any
Tags:	F_NEW FEATURE, handler

Description:
I cloned the latest sources from 
bk://mysql.bkbits.net/mysql-5.2-falcon today, and built mysqld.

SHOW TABLE STATUS doesn't seem to reflect any useful values for the falcon tables.  See this output:

mysql> drop table if exists falcon_table;
Query OK, 0 rows affected (0.02 sec)

mysql> create table falcon_table(id int)engine=falcon;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table falcon_table\G
*************************** 1. row ***************************
       Table: falcon_table
Create Table: CREATE TABLE `falcon_table` (
  `id` int(11) DEFAULT NULL
) ENGINE=Falcon DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show table status like 'falcon_table'\G
*************************** 1. row ***************************
           Name: falcon_table
         Engine: Falcon
        Version: 10
     Row_format: Fixed
           Rows: 1000
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.01 sec)

mysql> insert into falcon_table values (1),(2),(3),(4),(5),(6);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into falcon_table select * from falcon_table;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into falcon_table select * from falcon_table;
Query OK, 12 rows affected (0.02 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> insert into falcon_table select * from falcon_table;
Query OK, 24 rows affected (0.00 sec)
Records: 24  Duplicates: 0  Warnings: 0

mysql> insert into falcon_table select * from falcon_table;
Query OK, 48 rows affected (0.00 sec)
Records: 48  Duplicates: 0  Warnings: 0

mysql> insert into falcon_table select * from falcon_table;
Query OK, 96 rows affected (0.02 sec)
Records: 96  Duplicates: 0  Warnings: 0

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

mysql> show table status like 'falcon_table'\G
*************************** 1. row ***************************
           Name: falcon_table
         Engine: Falcon
        Version: 10
     Row_format: Fixed
           Rows: 1000
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.2.0-falcon-alpha-debug |
+--------------------------+
1 row in set (0.01 sec)

As you see, the SHOW TABLE STATUS didn't change after inserting, and is wrong.

How to repeat:
drop table if exists falcon_table;
create table falcon_table(id int)engine=falcon;
show create table falcon_table\G
show table status like 'falcon_table'\G
insert into falcon_table values (1),(2),(3),(4),(5),(6);
insert into falcon_table select * from falcon_table;
insert into falcon_table select * from falcon_table;
insert into falcon_table select * from falcon_table;
insert into falcon_table select * from falcon_table;
insert into falcon_table select * from falcon_table;
select count(*) from falcon_table;
show table status like 'falcon_table'\G

Bug #29862 was marked as duplicate of this one

Falcon does not keep exact statistics, but just enough to guide the 
optimizer.  Specifically, every table will be reported as having 
at least two records.  Different transactions may seen different 
numbers of records in a table using select count(*), but will not
necessarily see different table statistics.  However the behavior
has changed since the bug was reported and the number of records is
more accurate.  If this is satisfactory, we can close this bug. If
not, please describe the new features required.

Best regards,

Ann

mysql> create table t1 (f1 int) engine=falcon;
Query OK, 0 rows affected (0.64 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: Falcon
        Version: 10
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.09 sec)

mysql> insert into t1 values (1), (2), (3), (4), (5), (6);
Query OK, 6 rows affected (0.48 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> insert into t1 <etc.>...

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: Falcon
        Version: 10
     Row_format: Fixed
           Rows: 192
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|      192 |
+----------+
1 row in set (0.38 sec)

mysql> delete from t1;
Query OK, 192 rows affected (0.00 sec)

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

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: Falcon
        Version: 10
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.02 sec)

mysql>

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

Specifically I think we are looking for data length so we can gauge how large our tables are in comparison with other engines.

Ann, Let's put some thought into what it would take to maintain or gather statistics for SHOW TABLE STATUS.  What we are missing is 
 Avg_row_length: 0
    Data_length: 10000
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: NULL
    Update_time: NULL
     Check_time: NULL
       Checksum: NULL
 Create_options:
        Comment:

These are described here: 
  http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

If some of these fields can be satisfied with data already available,such as checksum, create_time, etc, then maybe we should use this bug to fill that data and open a new worklog to track the design and implementation of a solution to gather the rest of this data.