MySQL Bugs: #48848: myisamchk doing sort recover in certain cases resets data pointer to small size

Bug #48848	myisamchk doing sort recover in certain cases resets data pointer to small size
Submitted:	17 Nov 2009 20:00	Modified:	3 Feb 2012 16:29
Reporter:	Harrison Fisk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	5.1.40, 5.5.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	disable keys, MyISAMchk, sort recover

Description:
When you use myisamchk to repair a fixed-width table while the indexes are disabled using the sort recover method, it will reduce the MyISAM data pointer size to the smallest possible value for the rows in the table.

Naturally this is very bad since it will restrict the number of rows you can put in the table.

It seems to require the following to be true:

1. Fixed width table
2. Secondary indexes present
3. Indexes disabled via ALTER TABLE ... DISABLE KEYS 
4. Sort method done by myisamchk (either due to -n or larger enough values for -r)

How to repeat:
use test
drop table if exists t1;

# having a non-unique key on g is essential
create table t1 (i int auto_increment primary key, g int not null, key(g));
insert into t1 values (null, 1 );
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
#alter table t1 disable keys;

show table status like 't1'\G
flush tables;

myisamchk -n t1

show table status like 't1'\G

Suggested fix:
Leave the data pointer size as the larger value.

Oops, typo in the test case.  The alter table shouldn't be commented out naturally:

use test
drop table if exists t1;

# having a non-unique key on g is essential
create table t1 (i int auto_increment primary key, g int not null, key(g));
insert into t1 values (null, 1 );
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
insert into t1 select null, g+i from t1;
alter table t1 disable keys;

show table status like 't1'\G
flush tables;

myisamchk -n t1

show table status like 't1'\G

still affects 5.5.13. Just remember to add a ENGINE=myisam to the create table when testing 5.5

Noted in 5.1.62, 5.5.21, 5.6.5 changelogs.

Using myisamchk with the sort recover method to repair a table having
fixed-width row format could cause the row pointer size to be 
reduced, effectively resulting in a smaller maximum data file size.