Bug #16840 Duplicate Key error without keys being identical

• Submitted: 27 Jan 2006 13:22
• Modified: 10 Feb 2006 20:38
• Reporter: Michael Friedel
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: MySQL 5.0.18-standard
• OS: MacOS (OSX 10.4, RedHat 4)
• Assigned to: Evgeny Potemkin
• CPU Architecture: Any

[27 Jan 2006 13:22] Michael Friedel

Description:
I have a table that has a primary key of type char(16).

Whe I try to insert two different keys I get an duplicate key error.

INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360041, now(), 'DE', 0 ) ; 
INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360061, now(), 'DE', 0 ) ; 

select HEX(C_ID) from T_TEST where C_ID=0x820BF339CCBC4F24A93153AC8D360061;

returns

+----------------------------------+
| HEX(C_ID)                        |
+----------------------------------+
| 820BF339CCBC4F24A93153AC8D360041 |
+----------------------------------+

820BF339CCBC4F24A93153AC8D360041 != 820BF339CCBC4F24A93153AC8D360061

How to repeat:
create table T_TEST
(
   C_ID         char(16) not null,
   C_CREATED    datetime default '0000-00-00 00:00:00',
   C_COUNTRY    char(2)  default '0',
   C_STATUS     int,
   primary key (C_ID)
);

INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360021, now(), 'DE', 0 ) ; 
INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360031, now(), 'DE', 0 ) ; 
INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360041, now(), 'DE', 0 ) ; 
INSERT INTO T_TEST VALUES(0x820BF339CCBC4F24A93153AC8D360061, now(), 'DE', 0 ) ; 

select HEX(C_ID) from T_TEST where C_ID=0x820BF339CCBC4F24A93153AC8D360061;

[27 Jan 2006 22:12] Jorge del Conde

Thanks for your bug report.  I reproduced this under WinXP/5.0.18:

mysql> select HEX(C_ID) from T_TEST where C_ID=0x820BF339CCBC4F24A93153AC8D36006
1;
+----------------------------------+
| HEX(C_ID)                        |
+----------------------------------+
| 820BF339CCBC4F24A93153AC8D360041 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select 0x820BF339CCBC4F24A93153AC8D360041 = 0x820BF339CCBC4F24A93153AC8D3
60061;
+-------------------------------------------------------------------------+
| 0x820BF339CCBC4F24A93153AC8D360041 = 0x820BF339CCBC4F24A93153AC8D360061 |
+-------------------------------------------------------------------------+
|                                                                       0 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

[7 Feb 2006 7:26] Michael Friedel

I have made some progress. If I alter the table and set the BINARY flag it seems to work.

ALTER TABLE T_TEST MODIFY COLUMN C_ID CHAR(16) BINARY NOT NULL;

It does not work if I set the BINARY flag during the CREATE statement.

[10 Feb 2006 20:38] Evgeny Potemkin

Due to C_ID field in your table is char(16), mysqld treats '0x820BF339CCBC4F24A93153AC8D360061' as hex presentation of a string.
The keys 0x820BF339CCBC4F24A93153AC8D360061 and 0x820BF339CCBC4F24A93153AC8D360041 differs only in last byte - 0x61 and 0x41, which is the 'a' and 'A' letters in latin1 codepage. By default a case insensitive collation is set, thus these keys become identical.

In the query select 0x820BF339CCBC4F24A93153AC8D360041 = 0x820BF339CCBC4F24A93153AC8D360061;
these values compared as long long, so the result is 0.