Hi,

I am getting an duplicate key error while inserting a row where I don't think it is a duplicate primary key. Details of the bug are as following:

I am using mysql-3.23.14-alpha-sun-solaris2.6-sparc binary and the machine I am running mysql on is SunOS proactive1 5.6 Generic_105181-15 sun4u sparc SUNW,Ultra-5_10

I have a table created with the following statement.

create table PERS_OBJ_INSTANCES ( SEQNO numeric(12 ) not null, MOTYPEID numeric(12 ) not null, MOINSTANCEID numeric(12 ) not null, ATTRID numeric(12 ) not null, VALUE varchar(120) not null, primary key (SEQNO, MOTYPEID, MOINSTANCEID, ATTRID, VALUE ) ); please not that all columns are part of the primary key.

Now, INSERT INTO PERS_OBJ_INSTANCES VALUES (1, 1, 1, 1, 'a'); succeeds and then INSERT INTO PERS_OBJ_INSTANCES VALUES (1, 1, 1, 1, 'b'); fails with error

ERROR 1062: Duplicate entry '1-1-1-1-b' for key 1.

I have no clue as to why this should happen ? Duplicacy error should not be there if, value for even one column of the primary key is different and so the second stmt should succeed.

am I missing something here ?? Are there some constraints with mysql ?? I am perplexed since this behaviour violates the very basis of primary key.

One thing that I noticed is that the problem is there only if we have a column in primary key of datatype char or varchar. If all the datatypes are of type numeric, there seems to be no duplicate errors coming.

could you please check on this and respond as soon as possible. I will appreciate that very much.

thank you very much. regards.