Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Index not shown on MySQL 4.1 server

[expired user]'s profile image [expired user] posted 10 years ago in General Permalink
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
ansgar's profile image ansgar posted 10 years ago Permalink
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.
[expired user]'s profile image [expired user] posted 10 years ago Permalink
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.
ansgar's profile image ansgar posted 10 years ago Permalink
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?
[expired user]'s profile image [expired user] posted 10 years ago Permalink
"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!
Code modification/commit from ansgar.becker, 10 years ago, revision 4334
Support old style "TYPE BTREE" in table index code. See http://www.heidisql.com/forum.php?t=12150
ansgar's profile image ansgar posted 10 years ago Permalink
Please check if r4334 fixes that. I have no 4.1 server any more here for testing purposes.
[expired user]'s profile image [expired user] posted 10 years ago Permalink
Works fine. Thanks!
ansgar's profile image ansgar posted 10 years ago Permalink
Super.

Any chance you update your server in the near future?
[expired user]'s profile image [expired user] posted 10 years ago Permalink
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.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.