MySQL Bugs: #14616: Freshly imported table returns error 124 when using LIMIT

Bug #14616	Freshly imported table returns error 124 when using LIMIT
Submitted:	3 Nov 2005 17:56	Modified:	20 Nov 2005 4:04
Reporter:	Matthew Lord	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S1 (Critical)
Version:	5.0.15	OS:	Any (All)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
The problem seems to be myisam specific and is only triggered after a fresh import. A dump file
which displays the problem is attached, OasUser.sql. The problem does not occur if you import the
table as an InnoDB table.

How to repeat:
bash#mysql test < OasUser.sql
mysql>select firstname from test.OasUser order by firstname limit 1;

Interestingly, rebuilding the table fixes the problem:

mysql>alter table OasUser engine=myisam;
mysql>select firstname from OasUser order by firstname limit 1;

mysql> select firstname from test.OasUser order by firstname limit 1;
ERROR 1030 (HY000): Got error 124 from storage engine

I verified the problem with 4.1.15 and 4.0.24 as well so the cause must have something to do with the data itself.

I reproduced this on the current 4.0.27.
The problem is that MySQL tries to use the index 'name' though the indexes are still disabled after the load. A first workaround for the customer might be to issue an "alter table t1 enable keys;" after loading the table.
I'll try to find out why an index is used though all are disabled.
BTW, perror 124
System error: 124 = Wrong medium type
MySQL error:  124 = Wrong index given to function

This was indeed MyISAM related. Currently MyISAM is the only storage engine that can disable indexes. The fact that indexes could be disabled had been forgotten in the LIMIT evaluation.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32037

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32277

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32315

Pushed to 4.0.27, 4.1.16, and 5.0.17.

Noted in 4.0.27, 4.1.16, 5.0.17 changelogs.