Bug #5520 Insert statement failing on duplicate key
Submitted: 10 Sep 2004 18:33 Modified: 10 Sep 2004 20:58
Reporter: Elena Tivey Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.20-standard OS:pc-linux
Assigned to: CPU Architecture:Any

[10 Sep 2004 18:33] Elena Tivey
I have a simple table where Primary Key is one column type CHAR(2).

I'm trying to insert 2 records with the following values for the primary key:


It does not let me insert second record and the error is the following:

"ERROR 1062: Duplicate entry 'V@' for key 1"

When I convert the value into ascii, it gives me different values as it should:

select ascii('@')  - 64

select char(64) - @

select ascii('V') - 86

select ascii('v') - 118

But then when I try to insert both characters together as 'V@' or 'v@', it recognized them as the same.

I did the test and created a plain table with 2 columns and inserted 'V@' into column t1 and 'v@' into column t2

when I run the query:
   select * from test where t1 = t2

it selected back the row that I just inserted recognizing that both values are the same. 

The reason I need to get this to work is because we are converting certain data from Oracle to MySQL and this problem is not happening on Oracle side but when I bring the data over, I'm not able to insert the record that I need.

How to repeat:
Create table test with one field t1 and primary key on the field

Insert two records:

insert into test values('v@')

insert into test values ('V@') - the second record will fail on duplicate key

Suggested fix:
I'm not sure what would the be fix but it should not recognize V@ as being equal to v@
[10 Sep 2004 20:58] Hartmut Holzgraefe
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

Character fields are case insensitive unless you specify them as BINARY.

Create your column as 


and all will be as you expecet