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
[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: https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html
[25 Jun 2015 10:19]
li he
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;
[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.