Bug #41016 Maria: ALTER TABLE PACK_KEYS and MAX_ROWS has no effect
Submitted: 25 Nov 2008 14:18 Modified: 13 Jan 2009 8:12
Reporter: Guilhem Bichot Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Maria storage engine Severity:S3 (Non-critical)
Version:5.1-maria OS:Linux
Assigned to: CPU Architecture:Any

[25 Nov 2008 14:18] Guilhem Bichot
Description:
Running this test extracted from alter_table.test:
#
# Bug#39372 "Smart" ALTER TABLE not so smart after all.
#
create table t1(f1 int not null, f2 int not null, key  (f1), key (f2));
let $count= 50;
--disable_query_log
while ($count)
{
  EVAL insert into t1 values (1,1),(1,1),(1,1),(1,1),(1,1);
  EVAL insert into t1 values (2,2),(2,2),(2,2),(2,2),(2,2);
  dec $count ;
}
--enable_query_log

select index_length into @unpaked_keys_size from
information_schema.tables where table_name='t1';
alter table t1 pack_keys=1;
select index_length into @paked_keys_size from
information_schema.tables where table_name='t1';
select @unpaked_keys_size, @paked_keys_size;
select max_data_length into @orig_max_data_length from
information_schema.tables where table_name='t1';
alter table t1 max_rows=100;
select max_data_length into @changed_max_data_length from
information_schema.tables where table_name='t1';
select @orig_max_data_length, @changed_max_data_length;

I see that with a MyISAM table, @unpaked_keys_size > @paked_keys_size and
@orig_max_data_length > @changed_max_data_length, but with Maria, they are equal:
* select @unpaked_keys_size, @paked_keys_size;
  - MyISAM :  15360  3072
  - Maria:    24576  24576
* select @orig_max_data_length, @changed_max_data_length;
  - MyISAM : 2533274790395903       589823
  - Maria : 137438945280   137438945280

How to repeat:
see above

Suggested fix:
It may not be a bug at all, that's why I put it in "analyzing".
[13 Jan 2009 8:12] Michael Widenius
This is not a bug.

The reason that Maria file size is not changed is that for 2 indexes and a block size of 8K, 24576 is the smallest file size for the index file:

- 1 block for for Maria header
- 1 block for index 1
- 1 block for index 2

When running the test with a count of 500 (5000 rows) we get:

Maria with transactional=0
@unpaked_keys_size     @paked_keys_size
122880                 57344

and with transactional= 1
@unpaked_keys_size     @paked_keys_size
188416                 57344

(Note that the packed values for transactional is a little too good as alter table removes the transid's from the index)