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

User · Answer

i have just tried  and if you have data and table recreation wouldnt work  just alter table to InnoDB and try again  it would fix the problem

User · Answer

For me a noop on table has been enough  was already InnoDB   ALTER TABLE  tbl ENGINE InnoDB

User · Answer

In case anyone else finds this thread with my problem -- I was using an  integer  column type in MySQL  The row I was attempting to insert had a primary key with a value larger than allowed by integer  Switching to  bigint  fixed the problem

User · Answer

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

User · Answer

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

User · Answer

As per your code your  number  and  Name  are primarykey and you are inserting S NAME in both row so it will make a conflict  we are using primarykey for accessing complete data  here you cant access the data using the primarykey  name     im a beginner and i think it might be the error

User · Answer

Try with auto increment   CREATE TABLE IF NOT EXISTS  my table        number  int 11  NOT NULL AUTO INCREMENT      name  varchar 50  NOT NULL      money  int 11  NOT NULL      PRIMARY KEY   number   name     ENGINE MyISAM

User · Answer

This problem is often created when adding a column or using an existing column as a primary key  It is not created due to a primary key existing that was never actually created or due to damage to the table   What the error actually denotes is that a pending key value is blank    The solution is to populate the column with unique values and then try to create the primary key again  There can be no blank  null or duplicate values  or this misleading error will appear

User · Answer

Less common cases  but keep in mind that according to DOC https   dev mysql com doc refman 5 6 en innodb-online-ddl-limitations html     When running an online ALTER TABLE operation  the thread that runs the ALTER TABLE operation will apply an    online log    of DML operations that were run concurrently on the same table from other connection threads  When the DML operations are applied  it is possible to encounter a duplicate key entry error  ERROR 1062  23000   Duplicate entry   even if the duplicate entry is only temporary and would be reverted by a later entry in the    online log     This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction

User · Answer

As looking on your error  1062 - Duplicate entry  2-S  Name  for key  PRIMARY  it is saying that you use primary key in your number field that s why it is showing duplicate Error on Number Field   So Remove this primary Key then it inset duplicate also

User · Answer

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

User · Answer

In my case the error was very misleading  The problem was that PHPMyAdmin uses  ALTER TABLE  when you click on the  make unique  button instead of  ALTER IGNORE TABLE   so I had to do it manually  like in   ALTER TABLE mytbl ADD UNIQUE  columnName

User · Answer

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

User · Answer

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

User · Answer

Your code is work well on this demo   http   sqlfiddle com   8 87e10 1 0  I think you are doing second query  insert     twice  Try   select   from my table   before insert new row and you will get that your data already exist or not

[mysql] MySQL duplicate entry error even though there is no duplicate entry

Examples related to mysql

Examples related to primary-key

Examples related to mysql-error-1062

Examples related to duplicates