MySQL duplicate entry error even though there is no duplicate entry

Question

I am using MySQL 5.1.56, MyISAM. My table looks like this:

CREATE TABLE IF NOT EXISTS `my_table` (   `number` int(11) NOT NULL,   `name` varchar(50) NOT NULL,   `money` int(11) NOT NULL,   PRIMARY KEY (`number`,`name`) ) ENGINE=MyISAM; 

It contains these two rows:

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES (1, 'S. Name', 150), (2, 'Another Name', 284); 

Now I am trying to insert another row:

INSERT INTO `my_table` (`number`, `name`, `money`) VALUES (2, 'S. Name', 240); 

And MySQL just won't insert it while telling me this:

#1062 - Duplicate entry '2-S. Name' for key 'PRIMARY' 

I really don't understand it. The primary key is on the first two columns (both of them), so the row I am trying to insert HAS a unique primary key, doesn't it?

I tried to repair the table, I tried to optimize the table, all to no avail. Also please note that I cannot change from MyISAM to InnoDB.

Am I missing something or is this a bug of MySQL or MyISAM? Thanks.

To summarize and point out where I think is the problem (even though there shouldn't be): Table has primary key on two columns. I am trying to insert a row with a new combination of values in these two columns, but value in column one is already in some row and value in column two is already in another row. But they are not anywhere combined, so I believe this is supposed to work and I am very confused to see that it doesn't.

Answer 1

Your code and schema are OK. You probably trying on previous version of table.

http://sqlfiddle.com/#!2/9dc64/1/0

Your table even has no UNIQUE, so that error is impossible on that table.

Backup data from that table, drop it and re-create.

Maybe you tried to run that CREATE TABLE IF NOT EXIST. It was not created, you have old version, but there was no error because of IF NOT EXIST.

You may run SQL like this to see current table structure:

DESCRIBE my_table; 

---

Edit - added later:

Try to run this:

DROP TABLE `my_table`; --make backup - it deletes table  CREATE TABLE `my_table` (   `number` int(11) NOT NULL,   `name` varchar(50) NOT NULL,   `money` int(11) NOT NULL,   PRIMARY KEY (`number`,`name`),   UNIQUE (`number`, `name`) --added unique on 2 rows ) ENGINE=MyISAM; 

Answer 2

I know this wasn't the problem in this case, but I had a similar issue of "Duplicate Entry" when creating a composite primary key:

ALTER TABLE table ADD PRIMARY KEY(fieldA,fieldB);  

The error was something like:

#1062 Duplicate entry 'valueA-valueB' for key 'PRIMARY' 

So I searched:

select * from table where fieldA='valueA' and fieldB='valueB' 

And the output showed just 1 row, no duplicate!

After some time I found out that if you have NULL values in these field you receive these errors. In the end the error message was kind of misleading me.