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

[17 Nov 2009 20:00] Harrison Fisk
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.
[17 Nov 2009 20:13] Harrison Fisk
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
[29 Jun 2011 3:12] MySQL Verification Team
still affects 5.5.13. Just remember to add a ENGINE=myisam to the create table when testing 5.5
[3 Feb 2012 16:29] Paul DuBois
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.