Bug #2446 Index on TEXT column has problems when string length > prefix length
Submitted: 19 Jan 2004 9:19 Modified: 19 Jan 2004 11:48
Reporter: Dan Nelson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S3 (Non-critical)
Version:4.0.17 OS:Linux (Linux)
Assigned to: Dean Ellis CPU Architecture:Any

[19 Jan 2004 9:19] Dan Nelson
Description:
It looks like if you create an index on a text field, and specify to index the first X characters, inserting two fields longer than X (but identical from characters 1-X) will cause a duplicate key error.  Inserting duplicate fields shorter than X works fine.  

I can't reproduce the problem on 4.1.1, and I had someone try 4.0.16 and they couldn't reproduce it either.  The 4.0.17 machine is running the 4.0.17-MAX rpm from mysql's site.

How to repeat:
drop table if exists test;
create table test (
 filename text,
 id bigint unsigned NOT NULL auto_increment,
 PRIMARY KEY (id),
 KEY filename_idx (filename(30)),
) TYPE=MyISAM;
insert into test(filename) values ('tape-     -0001-000001-ALTAK            .hx');
insert into test(filename) values ('tape-     -0001-000001-ALTAK            .hy');

The 2nd insert will fail:

ERROR 1062: Duplicate entry 'tape-     -0001-000001-ALTAK            .hy' for key 2
[19 Jan 2004 11:48] Dean Ellis
This does occur in our 4.0.17 release, however I cannot repeat it with 4.0.18, so this appears to have already been corrected and the fix will be incorporated into our next release, or you can build 4.0.18 from our development sources.

Thank you.
[19 Jan 2004 12:20] Dan Nelson
Thanks for looking into it.  I'll just wait for 4.0.18, since it's easy enough to work around.