MySQL add/drop字段时报主键冲突

问题现象

很多DBA朋友做ddl 变更比如添加、删除字段时，一定概率上会遇到如下报错:

Duplicate entry '7458421' for key 'PRIMARY'

错误提示是主键冲突，但是当我们去查询 id= 7458421 时，并无此记录。是不是很奇怪？遇到这种情况，一般有如下场景:

1  表具有一个或者多个唯一键。2  表比较大，执行DDL耗时超过数十秒。3  表的insert 操作比较频繁。

问题分析

首先我们通过一个思维导图了解一下MySQL online DDL 的过程，大家注意commit阶段，会把ddl 执行期间的记录的 log 重新应用到新的表上。

从官方文档中的描述所说 online ddl 期间，其他会话执行的dml操作造成唯一键冲突的sql会记录到 online log 中，在commit阶段等变更结束之后再应用这些sql会导致报错唯一键冲突。

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.

问题复现

构造一个2000w记录的表,其表结构如下

CREATE TABLE `ddl` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `c1` int(10) NOT NULL DEFAULT '0',  `c2` int(10) unsigned NOT NULL DEFAULT '0',  `c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  UNIQUE KEY `c2` (`c2`),  KEY `idx_c1` (`c1`)) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=utf8mb4

举一反三 ，其实只要是会导致重复记录的sql语句，比如update，insert,insert into... on duplicate key,replace into 都会导致添加字段、删除字段的ddl变更失败。

如何解决呢 ，推荐使用 pt-osc或者 gh-ost 在线ddl变更工具

官方的讨论

官方定对于该问题是online ddl的限制，有兴趣的朋友可以阅读下面两个链接，了解官方和提交问题人员的讨论记录。

https://bugs.mysql.com/bug.php?id=76895

https://bugs.launchpad.net/percona-server/+bug/1445589

关联知识

innodb_online_alter_log_max_size 是MySQL 5.6版本引入。该参数限定了online ddl操作时使用的临时日志文件的最大大小。在创建索引或者对表进行alter操作时，该日志文件存储了DDL操作期间对表的 insert,update,delete的数据记录。临时日志文件每次以 innodb_sort_buffer_size 为单位进行扩展直至达到 innodb_online_alter_log_max_size设置的最大值。如果临时日志的大小超出规定限，则online ddl操作失败，当前所有未提交的DML操作会回滚。该参数设置日志文件太大带来的负面影响是可能会导致DDL操作最后锁定表(Waiting for table metadata lock)的时间更长，因为要花费更长的时间应用日志到表上。所以涉及到dml比较频繁的表的ddl 尽量放到业务低峰操作。