Bug #76895 Adding new column OR Drop column causes duplicate PK error
Submitted: 30 Apr 2015 6:06 Modified: 30 Apr 2015 14:02
Reporter: Nilnandan Joshi Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.6.24 OS:Any
Assigned to: CPU Architecture:Any

[30 Apr 2015 6:06] Nilnandan Joshi
When trying to add a column OR drop column to an InnoDB table the server reports an error about a duplicate PK.

nilnandan.joshi@bm-smm01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox5624.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER TABLE remote_archives DROP insert_worker, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1062 (23000): Duplicate entry '1379' for key 'PRIMARY'
mysql> ALTER TABLE remote_archives ADD insert_worker integer, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1060 (42S21): Duplicate column name 'insert_worker'
mysql> ALTER TABLE remote_archives DROP insert_worker, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (11.83 sec)
Records: 0  Duplicates: 0  Warnings: 0

Check more information here: https://bugs.launchpad.net/percona-server/+bug/1445589

How to repeat:
Check above
[30 Apr 2015 14:02] MySQL Verification Team
This is a very well known and fully documented limitation of our online ALTER operation. Here is the text:

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.

You can read about all limitation of online DDL here:

[25 Jun 2015 10:19] li he
can you describe more clearly. 
i encountered this 1062 error recently: alter table t add column a, whereas the dml statements on table t are only insert into values and select * from t.
i cannot figure out which case makes this error happened;
[4 Apr 2016 20:39] monty solomon
Can any of the logic added to fix bug #14723456 be applied here (I can't read that bug) ?
[19 Jul 2021 17:52] Jean-François Gagné
I was hit by this bug today, and obviously do not agree with the "very well known" limitation.

This comment is to make sure a work-around is out there, and this workaround is to use LOCK=EXCLUSIVE.
[19 Jul 2021 20:10] Jean-François Gagné
This also affects 8.0.25 and 5.7.31 (I cannot test with 5.7 latest on my MacBook because of Bug#102585).
[20 Jul 2021 9:14] Daniël van Eeden
This looks like some behaviour that is not user friendly. This might be documented but I'm sure most people encountering this might be puzzeled about why this happens and might not know how to handle this.

I think the user should not be concerned about how this happens behind the scenes and MySQL should make this work and/or give a more informative error message.

Maybe a retry would work? Or a change into how locking is used here.
[12 Aug 2021 22:15] Jean-François Gagné
Probably related: Bug#98600.
[12 Aug 2021 22:31] Jean-François Gagné
Related: Bug#104608.
[15 Aug 2021 17:57] Jean-François Gagné
related: Bug#104626.