alter table 出现duplicate primary xxx报错的原因及处理？

alter table 出现duplicate primary xxx报错的原因及处理？


一、原因分析
1、Online DDL操作时MySQL会将DML操作缓存起来存入到变更日志。
2、等到DDL执行完成后再应用变更日志中的DML操作。
3、在Oline DDL执行期间，并行的DML可能会没先检查唯一性直接插入一条相同主键的数据，这时并不会导致DDL报错，而是在DDL执行完成再次应用变更日志时才报错，最终导致DDL报错执行失败。


二、问题说明
其实这是Online DDL的正常情况，官方文档说明如下：
When running an in-place online DDL operation,
the thread that runs the ALTER TABLE statement applies 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.

ref：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-limitations.html


三、建议
1、推荐使用pt-osc、gh-ost等第三方工具进行DDL操作。
2、建议在业务低谷期进行操作。