Bug #28476 force index on a disabled myisam index gives error 124
Submitted: 16 May 2007 16:47 Modified: 7 Jun 2007 16:28
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:5.0.42 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any

[16 May 2007 16:47] Shane Bester
Description:
When a query wants to use an index, but that index is disabled, we get error 124 from myisam.

<cut>
mysql> SELECT `id` FROM `vhg` FORCE INDEX (`r`) WHERE `id`=1;
ERROR 1030 (HY000): Got error 124 from storage engine
</cut>

5.0.42 returns the above error.
5.1.19 doesn't give an error.

How to repeat:
DROP TABLE IF EXISTS `vhg`;
CREATE TABLE IF NOT EXISTS `vhg`(`id` TINYINT, KEY `r` (`id`))ENGINE=MyISAM;
INSERT INTO `vhg`(`id`) VALUES (1),(2),(3),(4),(5);
SELECT `id` FROM `vhg` FORCE INDEX (`r`) WHERE `id`=1;
ALTER TABLE `vhg` DISABLE KEYS;
SELECT `id` FROM `vhg` FORCE INDEX (`r`) WHERE `id`=1;
ALTER TABLE `vhg` ENABLE KEYS;
SELECT `id` FROM `vhg` FORCE INDEX (`r`) WHERE `id`=1;

Suggested fix:
not sure if this is a bug or not.
workaround is to run: ALTER TABLE .. ENABLE KEYS
[17 May 2007 9:14] Timour Katchaounov
Changed to P2 as this is wrong error instead of returning a query result.
[17 May 2007 13:14] Bugs System
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/commits/26904

ChangeSet@1.2491, 2007-05-17 16:14:16+03:00, gkodinov@magare.gmz +4 -0
  Bug #28476:
  When processing the USE/FORCE index hints
  the optimizer was not checking if the indexes 
  specified are enabled (see ALTER TABLE).
  Fixed by:
   - Ignoring disabled indexes in USE/FORCE index
   - issue warning if some of the USE/FORCE indexes is ignored.
[22 May 2007 12:11] Bugs System
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/commits/27118

ChangeSet@1.2491, 2007-05-22 13:28:02+03:00, gkodinov@magare.gmz +7 -0
  Bug #28476:
  When processing the USE/FORCE index hints
  the optimizer was not checking if the indexes 
  specified are enabled (see ALTER TABLE).
  Fixed by:
   Backporting the fix for bug 20604 to 5.0
[4 Jun 2007 21:21] Bugs System
Pushed into 5.1.20-beta
[4 Jun 2007 21:23] Bugs System
Pushed into 5.0.44
[7 Jun 2007 16:28] MC Brown
A note has been added to the 5.0.44 and 5.1.20 changelogs.