[mysql] 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 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','[email protected]','','');

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.

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

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

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.

you can try adding

$db['db_debug'] = FALSE; 

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

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.

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