Bug #20357 Got error 124 from storage engine using MIN and MAX functions in queries
Submitted: 9 Jun 2006 13:07 Modified: 16 May 2007 16:41
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.1,5.0,5.1 OS:Any (*)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[9 Jun 2006 13:07] Shane Bester
Description:
Queries using an index for MIN/MAX functions after an ALTER TABLE .. DISABLE KEYS statement return
"Got error 124 from storage engine" until ALTER TABLE .. ENABLE KEYS is run.

I tried 4.1BK, 5.0BK, 5.1BK, 5.0.21, 4.0.18 all giving same error.
4.0.27 Didn't show the error.

Here's output of the testcase result that fails:

....
mysql> ALTER TABLE `vhg` DISABLE KEYS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(`id`) FROM `vhg`;
ERROR 1030 (HY000): Got error 124 from storage engine
mysql> ALTER TABLE `vhg` ENABLE KEYS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT MAX(`id`) FROM `vhg`;
+-----------+
| MAX(`id`) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)
...

How to repeat:
DROP TABLE IF EXISTS `vhg`;
CREATE TABLE IF NOT EXISTS `vhg`(`id` TINYINT, KEY(`id`))ENGINE=MyISAM;
INSERT INTO `vhg`(`id`) VALUES (1);
SELECT MAX(`id`) FROM `vhg`;
ALTER TABLE `vhg` DISABLE KEYS;
SELECT MAX(`id`) FROM `vhg`;
ALTER TABLE `vhg` ENABLE KEYS;
SELECT MAX(`id`) FROM `vhg`;

Suggested fix:
not sure.
[16 Jun 2006 16:49] 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/7771
[26 Jun 2006 10:57] Sergey Vojtovich
Pushed into trees currently marked as 4.1.21, 5.0.23, 5.1.12.
[26 Jun 2006 14:05] Jon Stephens
Documented bugfix in 4.1.21/5.0.23/5.1.12 changelogs. Closed.
[16 May 2007 15:47] Shane Bester
5.0.42 gives error 124 for this testcase:

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;
[16 May 2007 16:41] Shane Bester
moving to a new bug report as this problem is not the same.