Bug #2725 KEY desc_idx (description(100)) being treated as UNIQUE KEY on Tru64
Submitted: 11 Feb 2004 23:55 Modified: 5 Mar 2004 0:31
Reporter: [ name withheld ] Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 OS:Tru62
Assigned to: Michael Widenius CPU Architecture:Any

[11 Feb 2004 23:55] [ name withheld ]
Description:
I have a table which includes a field and an index on the field as follow:

...
description text NOT NULL,
...
KEY desc_idx (description(100))

The key above is the 5th key on the table. This table has been in operation with versions of mysql from 3.23 through to 4.0.17 both on Tru64 and Linux (RedHat 7, 8 & 9). 

On Linux the key works as anticipated. It works as expected under Tru64 at least up to and including MySQL 4.0.12 (I have not try to replicate this event on any other versions between .12 and .17 on Tru64). I recently moved from 4.0.12 to 4.0.17 on Tru64 at which point I encountered problems (Duplicate Entry errors) with this key. I have been told that the term 'KEY' will be an alias to 'UNIQUE' in version 4.1 but I am not using that 4.1 and there is a difference in behaviour between 4.0.17 on Linux and Tru64.

If I drop the key under Tru64 4.0.17 the table will populate normally (i.e. be identical to that populated on Linux version 4.0.17 - but without the above KEY of course). If I then add the key after table population on Tru64 4.0.17 a reduction in the number of records relative to Linux version of same table results suggesting the Tru64 version 4.0.17 is implementing the KEY as a UNIQUE KEY, which is not expected behaviour. Dropping and re-applying the KEY under Linux 4.0.17 worked as expected with no loss of records. Re-implementation of version 4.0.12 on Tru64 and recreating the table allowed full population, i.e. restored the expect behaviour while re-implementation after that of 4.0.17 once again resulted again in the unexpected behaviour. It is thus repeatable in our environment.

Binary releases of all versions were used. I have only one Alpha on which to observe this occurence and it is a production server. I have not tested whether compling from source has the same unexpected behaviour. I can not provide data for you to test this or the rest of the table definition as it is as it is proprietary. I have reverted back to using 4.0.12 under Tru64 in the interim.

How to repeat:
I can not provide the table definition or data hence it is not possible for you to repeat this as in our environment. A partial table definition that may allow verification would be:

CREATE TABLE nr (
  nr_num varchar(20) NOT NULL default '',
  description text NOT NULL,
  PRIMARY KEY  (nr_num),
  KEY desc_idx (description(100))
) TYPE=MyISAM;
[5 Mar 2004 0:31] Michael Widenius
I tested the following on our true64 box:

> cd mysql-distrib/mysql-test

I then created file t/skr.test with following context:

####
drop table if exists t1;
CREATE TABLE t1 (
  nr_num varchar(20) NOT NULL default '',
  description text NOT NULL,
  PRIMARY KEY  (nr_num),
  KEY desc_idx (description(100))
) TYPE=MyISAM;

insert into t1 values("","test"),("a","test");
select count(*) from t1;
drop table t1;
####

Executed:

>./mysql-test-run --local --record --user=root t/skr.test
> cat cat r/skr.result

drop table if exists t1;
CREATE TABLE t1 (
nr_num varchar(20) NOT NULL default '',
description text NOT NULL,
PRIMARY KEY  (nr_num),
KEY desc_idx (description(100))
) TYPE=MyISAM;
insert into t1 values("","test"),("a","test");
select count(*) from t1;
count(*)
2
drop table t1;

As you can see, things worked perfectly for me.

If you want to help us find and fix the bug, please try to modify the
above case so that you get an error / wrong result on your machine and
attach the test to this bug report.