MySQL Bugs: #25066: Index type is always BTREE, can't be changed

Bug #25066	Index type is always BTREE, can't be changed
Submitted:	14 Dec 2006 11:43	Modified:	18 Dec 2006 14:23
Reporter:	Kari Söderholm	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.2.6	OS:	Windows (Windows XP)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	INDEX

Description:
When you try to change the index type of an index in the Table Editor to for example HASH (the default is BTREE) and click Apply Changes it shows the generated code for the modification.

For example:
ALTER TABLE `testdb`.`users` DROP INDEX `EMAIL`,
 ADD UNIQUE `EMAIL` USING HASH(`email`)
, ENGINE = MyISAM;

When you click Execute the confirmation window closes. Now when you select the index (on this example `EMAIL`) on the Indices tab of the Table Editor it still shows up as BTREE and not a HASH. Even if you close and reopen the Table Editor all Indexes still show up as BTREEs.

Either the Table Editor doesn't save the modification to the type of an index or it always incorrectly displays the type as BTREE even if it actually is something else.

How to repeat:
1. Create a new table with a few VARCHAR fields and Apply Changes.
2. Create a new index and drag one VARCHAR field into the Index Columns list and  Apply Changes.
3. Select the index you just created (Index_2 by default), change the Index Type to HASH and Apply Changes.
4. Select the index you just modified.

Now MySQL Administrator's Table Editor will show that the type of the index is still BTREE although it should be HASH. If you close the Table Editor and view the Schema Indices tab it will also show all indexes as BTREE.

Kari, please read the chapter about index types: http://dev.mysql.com/doc/refman/5.0/en/create-index.html. Only MEMORY tables can use HASH.

However, there is a related problem here: you cannot change the index type for the primary key. The code to determine the ALTER TABLE statement is missing the check for a changed index type.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html