Bug #2719 Heap tables status shows wrong or missing data.

• Submitted: 11 Feb 2004 20:38
• Modified: 22 Feb 2004 3:44
• Reporter: Peter Zaitsev (Basic Quality Contributor)
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Any (all)
• Assigned to: Bugs System
• CPU Architecture: Any

[11 Feb 2004 20:38] Peter Zaitsev

Description:
As you can see below information about heap table is incorrect or missing.

- data  length is 0 while there are 2 rows in this table
- index length is 0 while there is one btree index 
- max_data_length is just strange as max_heap_table_size is set to 16M 

+-------+------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name  | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+-------+------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| btree | HEAP | Fixed      |    0 |              5 |           0 |         3994575 |            0 |         0 |           NULL | NULL        | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+-------+------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> select @@max_heap_table_size;
+-----------------------+
| @@max_heap_table_size |
+-----------------------+
|              16777216 |
+-----------------------+
1 row in set (0.31 sec)

How to repeat:
CREATE TABLE `btree` (
  `i` int(11) default NULL,
  KEY `i` TYPE BTREE (`i`)
) ENGINE=HEAP

mysql> insert into i values (1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into i values (2);
Query OK, 1 row affected (0.00 sec)

[17 Feb 2004 19:14] MySQL Verification Team

Verified with a result a little different:

mysql> show table status\G
*************************** 1. row ***************************
           Name: btree
           Type: HEAP
     Row_format: Fixed
           Rows: 2
 Avg_row_length: 5
    Data_length: 129032
Max_data_length: 3994575
   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)

[22 Feb 2004 3:44] Victor Vagin

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

We only fixed value of Index_length for BTREE index..
All the other fields contain the actual information that we can give..
To avoid misunderstanding we added special note to manual about it..
(
For MEMORY (HEAP) tables, the Data_length,
Max_data_length, and Index_length values approximate the
actual amount of allocated memory. The allocation algorithm reserves memory
in large amounts to reduce the number of allocation operations.
)