29

I have a MySQL database with a table that has 2 million rows using innodb engine. I want to add another column, but I keep getting the following error:

Error 1062: Duplicate entry '' for key 'PRIMARY' SQL Statement: ALTER TABLE `mydb`.`table`  ADD COLUMN `country` VARCHAR(35) NULL DEFAULT NULL AFTER `email`

How can I add the column without getting this error?

EDIT: Table definition

id int(11) NOT NULL AUTO_INCREMENT,   
user_id varchar(45) NOT NULL,   
first_name varchar(150) DEFAULT NULL,   
last_name varchar(150) DEFAULT NULL,   
gender varchar(10) DEFAULT NULL,   
email varchar(100) DEFAULT NULL,   
created_at bigint(20) DEFAULT NULL,   
updated_at bigint(20) DEFAULT NULL,  
PRIMARY KEY (`id`,`user_id`),   
UNIQUE KEY `user_id_UNIQUE` (`user_id`),   
KEY `first_name` (`first_name`),   
KEY `last_name` (`last_name`)

EDIT #2: SHOW INDEXES output

Table       Non_unique  Key_name        Seq_in_index  Column_name     Collation  Cardinality Index_type
table       0           PRIMARY         1             id              A          3516446     BTREE      
table       0           PRIMARY         2             user_id         A          3516446     BTREE
table       0           user_id_UNIQUE  1             user_id         A          3516446     BTREE
table       1           first_name      1             first_name      A          390716      BTREE  
table       1           last_name       1             last_name       A          439555      BTREE
Improve this question
12
  • 1
    Try removing the after old_column. Commented Mar 22, 2014 at 16:50
  • 1
    Tried it, still produced the same error Commented Mar 22, 2014 at 16:54
  • 2
    Can you show your current table definition? Commented Mar 22, 2014 at 16:56
  • What does SHOW INDEXES FROM mydb.table return? Commented Mar 24, 2014 at 19:42
  • 2
    It's strange to define your PK over (id,user_id) here: it permits you to have multiple records with the same id value provided that their user_id values differ, however if you always rely on AUTO_INCREMENT then id values will necessarily differ. Furthermore, user_id values must always differ because of the user_id_UNIQUE constraint. This arrangement can lead to strange data, but I can't see how there can be duplicate PK values of '' unless the index has been corrupted. Commented Mar 24, 2014 at 19:52

7 Answers 7

Reset to default
29

it solution will lock table on write, but often suitable for solving the problem if table is not very big

LOCK TABLES my_table WRITE;

ALTER TABLE my_table 
ADD COLUMN `ts` DATETIME NULL AFTER `id`;

UNLOCK TABLES;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

24

As described in the documentation, 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.

Improve this answer

1 Comment

Best description of error. You can avoid this error with this tool : percona.com/doc/percona-toolkit/3.0/…
9

If it's not something a program needs to do (altering tables dynamically), then just wait a moment and then try again! It worked for me. :) I guess there are some InnoDB-specific processes/states (maybe still processing another ALTER queried just a moment ago?), during which the ALTER command would fail, and you just need to catch a moment when it succeeds.

Improve this answer

2 Comments

I had the same issue, and waiting for a long running process to end helped, so it might be a locking issue with a wrong error message.
This was our case as well. When we ran again after sometime it worked.
1

I've runned the same code and it works fine. http://sqlfiddle.com/#!2/1937e

As a solution, I would try to recreate the table , copy the data into it, and then switch tables using rename.

  • If that doesn't work then it's clear that it's a bug with your current mysql configuration, and we'll need more details to figure it out or at least reproduce it (mysql version, mysql config, database settings, the actual data, etc.).
  • If it works then it probably was a problem with tables or indexes and here are a few things you can check (you can also start with these if you don't wish to recreate the table):
    1. Check that you don't have any triggers that are causing other inserts
    2. Check that you are just creating a column and not adding other keys/indexes
    3. Check that the auto_increment value is not overflowing (for int it's over 2,000,000,000)

If none of the above, then you probably have a some corrupt data, or you missed to share some details.

Improve this answer

Comments

1

I guess there are some other ALTERs or INSERTs which are still processing.

First, to check the processing triggers with this following queries:

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.INNODB_TRX\G

Then, kill the locked query by trx_mysql_thread_id: 132092 for example:

KILL 132092;

Finally, here‘s a solution - OnlineSchemaChange (OSC). It built by Facebook supports online alter MySQL table schema with minimal impact.

Improve this answer

Comments

0

use change column

ALTER TABLE database.table_name CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (id);

Improve this answer

Comments

0

Use percona toolkit to change db scheme without needing to lock tables: https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html

This allows to continue updating db while scheme change is being applied. This especially useful for large dbs where updating scheme can take a while.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.