MySQL Bugs: #59359: Packed Status Always NULL

Bug #59359	Packed Status Always NULL
Submitted:	7 Jan 2011 18:07	Modified:	7 Jan 2011 18:43
Reporter:	James McKelvey	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.41, 5.5.9	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
SHOW INDEX FROM always shows the packed status of an index as NULL, whether it is packed or not. The only way to tell the status is to infer it from the index size, which only works if the table has sufficient rows.

How to repeat:
create database test;

use test;

create table x(value VARCHAR(10),
               index ind(value)) ENGINE=MyISAM, PACK_KEYS=1;

show index from x;

Shows PACKED as NULL.

Note that the value is NULL even for very large populated tables.

Indeed, even in current mysql-5.5 this columns still shows NULL, and to find out if pack_keys is set one has to use SHOW TABLE STATUS or SHOW CREATE TABLE:

mysql> show table status like 'x';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | 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 |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| x    | MyISAM |      10 | Dynamic    | 16384 |             20 |      327680 | 281474976710655 |       153600 |         0 |           NULL | 2011-01-07 20:17:44 | 2011-01-07 20:17:44 | 2011-01-07 20:17:44 | latin1_swedish_ci |     NULL | pack_keys=0    |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> alter table x pack_keys=1;
Query OK, 16384 rows affected (0.17 sec)
Records: 16384  Duplicates: 0  Warnings: 0

mysql> show table status like 'x';
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | 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 |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| x    | MyISAM |      10 | Dynamic    | 16384 |             20 |      327680 | 281474976710655 |        36864 |         0 |           NULL | 2011-01-07 20:19:40 | 2011-01-07 20:19:40 | 2011-01-07 20:19:40 | latin1_swedish_ci |     NULL | pack_keys=1    |         |
+------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> show index from x;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| x     |          1 | ind      |            1 | value       | A         |           1 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> show create table x\G
*************************** 1. row ***************************
       Table: x
Create Table: CREATE TABLE `x` (
  `value` varchar(10) DEFAULT NULL,
  KEY `ind` (`value`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=1
1 row in set (0.00 sec)

If the purpose of PACKED column in SHOW INDEX results is different, or if it is not used any more manual (http://dev.mysql.com/doc/refman/5.5/en/show-index.html) should explain this.

SHOW TABLE STATUS and SHOW CREATE TABLE only show the status of PACK_KEYS. But if PACK_KEYS is DEFAULT, a given index may be packed or unpacked. So I think that SHOW INDEX FROM really should show the proper status.