MySQL Bugs: #76895: Adding new column OR Drop column causes duplicate PK error

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:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.6.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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
owners.

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

mysql>
mysql> ALTER TABLE remote_archives DROP insert_worker, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1062 (23000): Duplicate entry '1379' for key 'PRIMARY'
mysql> 
mysql> ALTER TABLE remote_archives ADD insert_worker integer, ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1060 (42S21): Duplicate column name 'insert_worker'
mysql> 
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

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:

https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

HI,Sinisa 
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;

Can any of the logic added to fix bug #14723456 be applied here (I can't read that bug) ?

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.

This also affects 8.0.25 and 5.7.31 (I cannot test with 5.7 latest on my MacBook because of Bug#102585).

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.

Probably related: Bug#98600.

Related: Bug#104608.

related: Bug#104626.