MySQL error - 1062 - Duplicate entry for key 2

Question

I m trying to insert a huge list of users to a MySQL database but everytime I try I get the error     1062 - Duplicate entry    for key 2   It gives me this because the 2nd column is blank on quite a lot of the entries  so after it s inserted one blank entry in column 2  it won t let me add another  However  when I added most of the list yesterday I didn t get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well  Whats going on    This is the sql code to insert 1 entry  The rest follow the same format   INSERT INTO users     id   title   firstname   lastname   company   address   city   county       postcode   phone   mobile   category   email   password   userlevel    VALUES                John   Doe   company   Streeet   city   county      postcode   phone      category   emial email co uk

User · Answer

I got this error when I tried to set a column as unique when there was already duplicate data in the column OR if you try to add a column and set it as unique when there is already data in the table.

I had a table with 5 rows and I tried to add a unique column and it failed because all 5 of those rows would be empty and thus not unique.

I created the column without the unique index set, then populated the data then set it as unique and everything worked.

User · Answer

In addition to Sabeen s answer   The first column id is your primary key  Don t insert    into the primary key  but insert null instead   INSERT INTO users     id   title   firstname   lastname   company   address   city   county       postcode   phone   mobile   category   email   password   userlevel    VALUES         null     John   Doe   company   Streeet   city   county      postcode   phone      category   emial email co uk            If it s an autoincrement key this will fix your problem  If not make id an autoincrement key  and always insert null into it to trigger an autoincrement   MySQL has a setting to autoincrement keys only on null insert or on both inserts of 0 and null  Don t count on this setting  because your code may break if you change server  If you insert null your code will always work   See  http   dev mysql com doc refman 5 0 en example-auto-increment html

User · Answer

Seems like the second column is set as a unique index  If you dont need that remove it and your errors will go away  Possibly you added the index by mistake and thats why you are seeing the errors today and werent seeing them yesterday

User · Answer

Drop the primary key first   The primary key is your responsibility   ALTER TABLE Persons DROP PRIMARY KEY    Then make all insertions   Add new primary key just like before dropping   ALTER TABLE Persons ADD PRIMARY KEY  P Id

User · Answer

Check out your table index  If that field got index  e g  B  tree index   At that time update or insert query throws these kinds of error   Remove indexing and then try to fire same query

User · Answer

As it was said you have a unique index       However  when I added most of the list yesterday I didn t get this error once even though a lot of the entries I added yesterday have a blank cell in column 2 as well  Whats going on    That means that all these entries contain value NULL  not empty string     Mysql lets you have multiple NULL values in unique fields

User · Answer

you can try adding    db  db debug     FALSE     in  your database file  php after that you can modify your database as you like

User · Answer

Error  Duplicate entry  338620-7  for key 2  on query  Default database   For this error    set global sql slave skip counter 1   start slave   show slave status G   This worked for me

[mysql] MySQL error - #1062 - Duplicate entry ' ' for key 2

Examples related to mysql

Examples related to database