Index not shown on MySQL 4.1 server

Hi,
my coworker just told me, that he can´t see a table-index in HeidiSQL while the MySQL Administrator tool from MySQL (ancestor of the MySQL Workbench) shows it. The server version is 4.1.12

If I restore a backup in a 5.5 database, HeidiSQL shows the index correctly, but if I restore it to the 4.1 server (in another database) the index will not be shown.

Here´s the create syntax (minified, but problem still occours):
CREATE TABLE `a_master_mn` (
`aMmnId` bigint(20) unsigned NOT NULL auto_increment,
`aMmnM` bigint(20) unsigned default NULL,
`aMmnN` bigint(20) unsigned default NULL,
PRIMARY KEY (`aMmnId`),
UNIQUE KEY `iMNRecursion` TYPE BTREE (`aMmnM`,`aMmnN`)
) ENGINE=InnoDB;

Any idea?

Greetings
Plasm

Looks like the syntax for "TYPE BTREE" has changed after 4.1 at some point, into "USING BTREE" after the columns list. HeidiSQL only detects the newer syntax. I'm not sure if I should still fix stuff for such old servers, however.

Because it is a lot of work? Or is it a matter of principle?
It´s a pity, since I can´t be sure that HeidiSQL shows the reality.

It's more difficult than a matter of principle, when you have myriads of bugreports and feature requests in your back. However, I'm not even sure what I wrote about TYPE... is true. Can you shed some light on that change in later server versions?

"Before MySQL 5.0.60, this option can be given only before the ON tbl_name clause. Use of the option in this position is deprecated as of 5.0.60 and support for it there will be removed in a future MySQL release."
Source: Bottom of http://dev.mysql.com/doc/refman/5.0/en/create-index.html

I tried to execute the following (newer) syntax at the 4.1 server:

CREATE TABLE `a_master_mn` (
`aMmnId` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`aMmnM` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
`aMmnN` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`aMmnId`),
UNIQUE INDEX `iMNRecursion` (`aMmnM`, `aMmnN`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Result (as expected): "Syntax error near USING BTREE) COLLATE..."

I guess you use "SHOW CREATE TABLE a_master_mn;" and parse the result to see what keys exists? Perhaps give "SHOW INDEX FROM a_master_mn;" a try, since this command provides exact the same result for the table on the 4.1 and a 5.5 server.

Hope this helps!

Please check if r4334 fixes that. I have no 4.1 server any more here for testing purposes.

Works fine. Thanks!

Super.

Any chance you update your server in the near future?

New server hardware is already bought, but I think it will last at least one more year until "they" replace the old servers. That is not under my control :(

Anyway, I work myself on a 5.5 MySQL server. I seldom have to deal with the 4.1 servers. The old servers are mainly used by older (important) projects from other programmers (but they use HeidiSQL as well).