Index not shown on MySQL 4.1 server

User, date Message
Written by Plasm
4 months ago
Category: General
64 posts since Fri, 10 Sep 10 		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
Written by ansgar
4 months ago
4027 posts since Fri, 07 Apr 06 		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.
Written by Plasm
4 months ago
64 posts since Fri, 10 Sep 10 		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.
Written by ansgar
4 months ago
4027 posts since Fri, 07 Apr 06 		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?
Written by Plasm
4 months ago
64 posts since Fri, 10 Sep 10 		"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!
Written by ansgar
4 months ago
4027 posts since Fri, 07 Apr 06 		Please check if r4334 fixes that. I have no 4.1 server any more here for testing purposes.
Written by Plasm
4 months ago
64 posts since Fri, 10 Sep 10 		Works fine. Thanks!
Written by ansgar
4 months ago
4027 posts since Fri, 07 Apr 06 		Super.

Any chance you update your server in the near future?
Written by Plasm
4 months ago
64 posts since Fri, 10 Sep 10 		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).
 

Please login to leave a reply, or register at first.