MySQL Bugs: #13478: Got error 124 from the storage engine

Bug #13478	Got error 124 from the storage engine
Submitted:	26 Sep 2005 9:04	Modified:	27 Sep 2005 10:27
Reporter:	P S	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:		OS:	Linux (RH EL4)
Assigned to:		CPU Architecture:	Any

Description:
Hi there, 

Following is my table definition: 

CREATE TABLE `User` ( 
`DefAcct` varchar(32) default NULL, 
`Email` varchar(64) default NULL, 
`Fax` varchar(20) default NULL, 
`FName` varchar(32) NOT NULL default '', 
`Id` varchar(50) NOT NULL default '', 
`Pwd` varchar(50) NOT NULL default '', 
`LName` varchar(32) NOT NULL default '', 
`Notes` text, 
`UserKey` int(11) NOT NULL auto_increment, 
`UserPKey` int(11) NOT NULL default 0, 
`Phone` varchar(20) default NULL, 
`RState` char(1) default 'L', 
`Title` varchar(64) NOT NULL default '', 
`Category` char(1) default 'I', 
`WhenCreated` datetime default NULL, 
`WhenModified` datetime default NULL, 
`WhoCreated` varchar(50) default NULL, 
`WhoModified` varchar(50) default NULL, 
PRIMARY KEY (`UserKey`), 
UNIQUE KEY `Id` (`Id`), 
KEY `Name` (`FName`,`LName`), 
KEY `Title` (`Title`) 
); 

I have added 40 rows in the table. 
Some of the rows contains a space for FName and Title. 

When I run the following query: 

Select * from User order by FName limit 0,10; 

It gives me the following error: 
Got error 124 from storage engine 

When I run the following query: 

Select * from User order by FName limit 0,40; 

It works fine. 

The version of MySQL which I am using is 5.x. 

Please reply ASAP. 

Regards, 
PS

How to repeat:
INSERT INTO `User` VALUES ('','','','fn1','user1','user1','ln1','',1,1,'','L','','I','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn2','user2','user2','ln2','',2,1,'','L','','I','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn3','user3','user3','ln3','',3,1,'','L','','I','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn4','user4','user4','ln4','',4,1,'','L','','I','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn5','user5','user5','ln5','',5,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn6','user6','user6','ln6','',6,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn7','user7','user7','ln7','',7,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn8','user8','user8','ln8','',8,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn9','user9','user9','ln9','',9,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn10','user10','user10','ln10','',10,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn11','user11','user11','ln11','',11,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn12','user12','user12','ln12','',12,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn13','user13','user13','ln13','',13,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');
INSERT INTO `User` VALUES ('','','','fn14','user14','user14','ln14','',14,1,'','L','','E','2005-06-13 22:32:47','2005-06-13 22:32:47','admin','admin');

Please insert the above statements in the table and run the above mentioned query

Please, specify the storage engine used (the results of SHOW CREATE TABLE ... will be enough), and, please, give the exact version: 5.0.12 or whatever. Can be got from mysql (or any) client with:

SELECT version();

Hi,

The version of MySql is : 5.0.7-beta-standard

The storage engine is : MyISAM

Can you please let me know, what is the issue?

Regards,
PS.

Please, try to use newer version, at least, 5.0.12. I was not able to repeat with it the problem you described:

mysql> CREATE TABLE `User` (
    -> `DefAcct` varchar(32) default NULL,
    -> `Email` varchar(64) default NULL,
    -> `Fax` varchar(20) default NULL,
    -> `FName` varchar(32) NOT NULL default '',
    -> `Id` varchar(50) NOT NULL default '',
    -> `Pwd` varchar(50) NOT NULL default '',
    -> `LName` varchar(32) NOT NULL default '',
    -> `Notes` text,
    -> `UserKey` int(11) NOT NULL auto_increment,
    -> `UserPKey` int(11) NOT NULL default 0,
    -> `Phone` varchar(20) default NULL,
    -> `RState` char(1) default 'L',
    -> `Title` varchar(64) NOT NULL default '',
    -> `Category` char(1) default 'I',
    -> `WhenCreated` datetime default NULL,
    -> `WhenModified` datetime default NULL,
    -> `WhoCreated` varchar(50) default NULL,
    -> `WhoModified` varchar(50) default NULL,
    -> PRIMARY KEY (`UserKey`),
    -> UNIQUE KEY `Id` (`Id`),
    -> KEY `Name` (`FName`,`LName`),
    -> KEY `Title` (`Title`)
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `User` VALUES
    -> ('','','','fn1','user1','user1','ln1','',1,1,'','L','','I','2005-06-13
    '> 22:32:47','2005-06-13 22:32:47','admin','admin');
Query OK, 1 row affected (0.02 sec)
...

mysql> INSERT INTO `User` VALUES
    -> ('','','','fn14','user14','user14','ln14','',14,1,'','L','','E','2005-06
13
    '> 22:32:47','2005-06-13 22:32:47','admin','admin');
Query OK, 1 row affected (0.00 sec)

mysql> Select * from User order by FName limit 0,10;
+---------+-------+------+-------+--------+--------+-------+-------+---------+---------+-------+--------+-------+----------+---------------------+--------------------+------------+-------------+
| DefAcct | Email | Fax  | FName | Id     | Pwd    | LName | Notes | UserKey |
serPKey | Phone | RState | Title | Category | WhenCreated         | WhenModifie
        | WhoCreated | WhoModified |
+---------+-------+------+-------+--------+--------+-------+-------+---------+---------+-------+--------+-------+----------+---------------------+--------------------+------------+-------------+
|         |       |      | fn1   | user1  | user1  | ln1   |       |       1 |
      1 |       | L      |       | I        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn10  | user10 | user10 | ln10  |       |      10 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn11  | user11 | user11 | ln11  |       |      11 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn12  | user12 | user12 | ln12  |       |      12 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn13  | user13 | user13 | ln13  |       |      13 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn14  | user14 | user14 | ln14  |       |      14 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn2   | user2  | user2  | ln2   |       |       2 |
      1 |       | L      |       | I        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn3   | user3  | user3  | ln3   |       |       3 |
      1 |       | L      |       | I        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn4   | user4  | user4  | ln4   |       |       4 |
      1 |       | L      |       | I        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
|         |       |      | fn5   | user5  | user5  | ln5   |       |       5 |
      1 |       | L      |       | E        | 2005-06-13 22:32:47 | 2005-06-13
2:32:47 | admin      | admin       |
+---------+-------+------+-------+--------+--------+-------+-------+---------+---------+-------+--------+-------+----------+---------------------+--------------------+------------+-------------+
10 rows in set (0.00 sec)

mysql> Select * from User order by FName limit 0,40;
...
14 rows in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.12-beta-nt |
+----------------+
1 row in set (0.00 sec)

But I think, the real reason is that you just have a corrupted table. Look:

D:\tmp\vk>perror 124
MySQL error code 124: Wrong index given to function

And as you can check with EXPLAIN, your first query use index (Name), and second select all rows from the table. So, try to repair the table.

In any case, try to repeat with a newer version and send the comment in case the problem still exists. I can not repeat it.

+--------------------+
| version()          |
+--------------------+
| 5.0.13-rc-standard |
+--------------------+

I have used this version.

Hi,

The problem still persists with the upgraded version. Can you please advise me, whats the issue?.

Regards,
PS.

Hi P S,

Please, try the following quick experiment.

ALTER TABLE User ENABLE INDEXES;
Select * from User order by FName limit 0,10;

Let us know if that causes the error to go away.

I encountered the same problem after restoring a database from a backup. The database consisted of myisam and merge tables. The problem disappeared after command 
flush tables;

btw, mysql version() 5.0.22, centos