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.

Solution 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;

Solution 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.

Solution 3

I had a similar issue, but in my case it turned out that I used case insensitive collation - utf8_general_ci.

Thus, when I tried to insert two strings which were different in a case-sensitive comparison, but the same in the case-insensitive one, MySQL fired the error and I couldn't understand what a problem, because I used a case-sensitive search.

The solution is to change the collation of a table, e.g. I used utf8_bin which is case-sensitive (or utf8_general_cs should be appropriate one too).

Solution 4

In my case the error was caused by the outdated schema, one column was originally varchar(50) but the dump I was trying to import was created from a modified version of the schema that has varchar(70) for that column (and some of the entries of that field where using more than 50 chars).

During the import some keys were truncated and the truncated version was not unique anymore. Took a while to figure that out, I was like "but this supposedly duplicated key doesn't even exist!".

Solution 5

In case this helps anyone besides the OP, I had a similar problem using InnoDB.

For me, what was really going on was a foreign key constraint failure. I was referencing a foreign key that did not exist.

In other words, the error was completely off. The primary key was fine, and inserting the foreign key first fixed the problem. No idea why MySQL got this wrong suddenly.

View more solutions