distal-attribute
distal-attribute
distal-attribute
distal-attribute

Index not shown on MySQL 4.1 server

User, date Message
Written by Plasm
1 year ago
Category: General
72 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
1 year ago
4784 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
1 year ago
72 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
1 year ago
4784 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
1 year ago
72 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
1 year ago
4784 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
1 year ago
72 posts since Fri, 10 Sep 10
Works fine. Thanks!
Written by ansgar
1 year ago
4784 posts since Fri, 07 Apr 06
Super.

Any chance you update your server in the near future?
Written by Plasm
1 year ago
72 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.