| Bug #2754 | Index error when using order by and limit on indexes | ||
|---|---|---|---|
| Submitted: | 12 Feb 2004 10:24 | Modified: | 26 Mar 2004 11:05 |
| Reporter: | Jeremy Lichfield | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S1 (Critical) |
| Version: | 4.0.17 | OS: | MacOS (Mac OS X and RedHat 9) |
| Assigned to: | CPU Architecture: | Any | |
[24 Feb 2004 10:57]
MySQL Verification Team
Can't repeat it with 4.0.18. We need a snippet of your table that would help us reproduce it. You can upload it here, under "Files" tab.
[24 Feb 2004 10:58]
MySQL Verification Team
This is what I have got:
mysql> CREATE TABLE USERS_MAIN (
-> ID bigint(20) NOT NULL auto_increment,
-> DATE_CREATED datetime NOT NULL default '0000-00-00 00:00:00',
-> DATE_MOD datetime default NULL,
-> MERIT_DATE date NULL default NULL,
-> RELATIONSHIP varchar(40) NOT NULL default '',
-> TIME_ZONE enum('mt','at','ht','pt','ct','et','lt') NOT NULL default 'mt',
-> ACTIVE enum('y','n') NOT NULL default 'y',
-> STATUS enum('on','off') NOT NULL default 'on',
-> FIRST_NAME varchar(25) NOT NULL default '',
-> LAST_NAME varchar(25) NOT NULL default '',
-> USERNAME varchar(20) NOT NULL default '',
-> PASSWORD varchar(20) NOT NULL default '',
-> SID varchar(20) default NULL,
-> LOCK_OUT int(1) default NULL,
-> LOCKED enum('n','y') NOT NULL default 'n',
-> ADMIN_USERNAME bigint(20) default NULL,
-> IP_ADDRESS varchar(20) default NULL,
-> PRIMARY KEY (ID),
-> UNIQUE KEY USERNAME (USERNAME),
-> KEY RELATIONSHIP (RELATIONSHIP),
-> KEY FIRST_NAME (FIRST_NAME),
-> KEY LAST_NAME (LAST_NAME),
-> KEY ACTIVE (ACTIVE),
-> KEY STATUS (STATUS),
-> KEY SID (SID),
-> KEY LOCK_OUT (LOCK_OUT),
-> KEY LOCKED (LOCKED)
-> ) TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show tables;
+---------------+
| Tables_in_bug |
+---------------+
| USERS_MAIN |
+---------------+
1 row in set (0.00 sec)
mysql> select ID,LOCKED,FIRST_NAME,LAST_NAME,RELATIONSHIP,STATUS from USERS_MAIN where
-> ACTIVE
-> = 'y' and RELATIONSHIP != 'parent' order by RELATIONSHIP limit 0, 50;
Empty set (0.08 sec)
[26 Feb 2004 11:05]
Jeremy Lichfield
I dumped the database tables and reloaded it. Now I don't get any errors? I'm not sure what was wrong now? I know the repair table syntax did not work. Thanks for your help. Jeremy
[14 Feb 2005 22:54]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Index error when using order by and limit on indexes. Here is an example: CREATE TABLE USERS_MAIN ( ID bigint(20) NOT NULL auto_increment, DATE_CREATED datetime NOT NULL default '0000-00-00 00:00:00', DATE_MOD datetime default NULL, MERIT_DATE date NULL default NULL, RELATIONSHIP varchar(40) NOT NULL default '', TIME_ZONE enum('mt','at','ht','pt','ct','et','lt') NOT NULL default 'mt', ACTIVE enum('y','n') NOT NULL default 'y', STATUS enum('on','off') NOT NULL default 'on', FIRST_NAME varchar(25) NOT NULL default '', LAST_NAME varchar(25) NOT NULL default '', USERNAME varchar(20) NOT NULL default '', PASSWORD varchar(20) NOT NULL default '', SID varchar(20) default NULL, LOCK_OUT int(1) default NULL, LOCKED enum('n','y') NOT NULL default 'n', ADMIN_USERNAME bigint(20) default NULL, IP_ADDRESS varchar(20) default NULL, PRIMARY KEY (ID), UNIQUE KEY USERNAME (USERNAME), KEY RELATIONSHIP (RELATIONSHIP), KEY FIRST_NAME (FIRST_NAME), KEY LAST_NAME (LAST_NAME), KEY ACTIVE (ACTIVE), KEY STATUS (STATUS), KEY SID (SID), KEY LOCK_OUT (LOCK_OUT), KEY LOCKED (LOCKED) ) TYPE=MyISAM; select ID,LOCKED,FIRST_NAME,LAST_NAME,RELATIONSHIP,STATUS from USERS_MAIN where ACTIVE = 'y' and RELATIONSHIP != 'parent' order by RELATIONSHIP limit 0, 50; ERROR 1030: Got error 124 from table handler How to repeat: Here is an example: CREATE TABLE USERS_MAIN ( ID bigint(20) NOT NULL auto_increment, DATE_CREATED datetime NOT NULL default '0000-00-00 00:00:00', DATE_MOD datetime default NULL, MERIT_DATE date NULL default NULL, RELATIONSHIP varchar(40) NOT NULL default '', TIME_ZONE enum('mt','at','ht','pt','ct','et','lt') NOT NULL default 'mt', ACTIVE enum('y','n') NOT NULL default 'y', STATUS enum('on','off') NOT NULL default 'on', FIRST_NAME varchar(25) NOT NULL default '', LAST_NAME varchar(25) NOT NULL default '', USERNAME varchar(20) NOT NULL default '', PASSWORD varchar(20) NOT NULL default '', SID varchar(20) default NULL, LOCK_OUT int(1) default NULL, LOCKED enum('n','y') NOT NULL default 'n', ADMIN_USERNAME bigint(20) default NULL, IP_ADDRESS varchar(20) default NULL, PRIMARY KEY (ID), UNIQUE KEY USERNAME (USERNAME), KEY RELATIONSHIP (RELATIONSHIP), KEY FIRST_NAME (FIRST_NAME), KEY LAST_NAME (LAST_NAME), KEY ACTIVE (ACTIVE), KEY STATUS (STATUS), KEY SID (SID), KEY LOCK_OUT (LOCK_OUT), KEY LOCKED (LOCKED) ) TYPE=MyISAM; select ID,LOCKED,FIRST_NAME,LAST_NAME,RELATIONSHIP,STATUS from USERS_MAIN where ACTIVE = 'y' and RELATIONSHIP != 'parent' order by RELATIONSHIP limit 0, 50; ERROR 1030: Got error 124 from table handler Notice when I do an explain it is not using filesort: +------------+-------+---------------+--------------+---------+------+------ +-------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +------------+-------+---------------+--------------+---------+------+------ +-------------+ | USERS_MAIN | index | NULL | RELATIONSHIP | 40 | NULL | 76 | Using where | +------------+-------+---------------+--------------+---------+------+------ +-------------+ Suggested fix: I'm not an expert at this, however I do know that this same querry worked on earilier versions of mysql: 3.23. It appears it is not recognizing the order by key?