mysql onlibe all_怪异的MySQL Online DDL报错Duplicate entry

今天线上执行Online DDL的时候发现一个奇怪的报错，觉得比较意义，遂整理如下。线上数据库版本：percona server 5.7.14

报错现场：每次执行的时候重复报错记录都不一样

mysql> ALTER TABLE `xxx` modify `bm_pay_trade_no` varchar(50) NOT NULL COMMENT 'xxxx';

ERROR 1062 (23000): Duplicate entry 'sss' for key 'uniq_xxx'

mysql> select * from xxx where xxxx='sss';

...此处只有一条记录，并没有重复值...

按照常理来说这列有唯一索引约束不应该报错的哈，更不会产生Duplicate entry。我隐约记得这个问题很熟悉，貌似之前在哪遇到过。可以看我之前文章MySQL Online DDL和MySQL在线DDL工具pt-osc。

看看官网的解释吧：https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-limitations.html

When running an 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.

也就是说可能有其他线程的DML产生了临时的重复条目，导致的唯一性约束监测报错。理解到这里我觉得就差不多，然后想起来pt-osc的工作原理(MySQL在线DDL工具pt-osc)，遂豁然开朗。

问题的解决就是用pt-osc来执行这个变更操作，问题完美得到解决。

为了方便大家交流，本人开通了微信公众号，和QQ群1(291519319)和QQ群2(659336691)。喜欢技术的一起来交流吧