MySQL error - #1062 - Duplicate entry ' ' for key 2

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 aft开发者_如何学Goer 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','','');

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

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

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.

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.

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.

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

you can try adding

$db['db_debug'] = FALSE; 

in "your database file".php after that you can modify your database as you like.

1. Drop the primary key first: (The primary key is your responsibility)

   ALTER TABLE Persons DROP PRIMARY KEY ;
   

2. Then make all insertions:

3. Add new primary key just like before dropping:

   ALTER TABLE Persons ADD PRIMARY KEY (P_Id);