MySQL DROP字段报主键冲突

前言

此案例介绍MySQL5.7 DROP字段碰到的bug。

1 环境信息

自建MySQL：5.7.22

OS 版本：CentOS Linux release 7.6

2 错误描述

开发反馈某表执行drop字段时，执行一段时间后，会遇到报错 "[Err] 1062 - Duplicate entry '8014980' for key 'PRIMARY'" 而执行失败。重复执行最终也会因上述报错而失败，并且每次报错的主键entry值不一样，且表里并无此主键值。

语句为：

ALTER table t1 DROP COLUMN col6 ;

3 排查

登录报错实例，后台执行上述alter语句报相同错误。

[Err] 1062 - Duplicate entry '8015300' for key 'PRIMARY'

3.1 查看表结构

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `col1` varchar(50) NOT NULL,
  `col2` varchar(64) NOT NULL,
  `col3` varchar(64) NOT NULL,
  `col4` varchar(64) NOT NULL,
  `col5` varchar(50) DEFAULT NULL,
  `col6` int(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_1` (`col2`,`col4`,`col5`),
  KEY `idx_col3` (`col3`)
) ENGINE=InnoDB AUTO_INCREMENT=8026847 DEFAULT CHARSET=utf8 COMMENT='XXXXXX'
1 row in set (0.00 sec)

4 原因

根据报错，有人提过bug，但官方不认为是bug，是Online DDL限制导致。

5.7版本加字段，ALGORITHM=INPLACE这个是默认加到alter后面的，因为加字段能用online ddl，原地重建表，允许并发dml。1062报错触发了online ddl的限制

4.1 bug链接

链接参考：

MySQL bug链接：https://bugs.mysql.com/bug.php?id=76895

Percona bug链接：https://bugs.launchpad.net/percona-server/+bug/1445589

4.2 问题描述

Description:
When trying to add a column OR drop column to an InnoDB table the server reports an error about a duplicate PK.

4.3 官方解释

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.
执行 Online DDL 操作时，该线程 ALTER TABLE 操作的同时，其他连接线程可能会对同一张表进行并发INSERT/UPDATE/DELETE这类DML操作，并且这类操作日志会写入缓存中（大小由innodb_online_after_log_max_size控制）。ALTER TABLE 操作修改完表结构后，会应用这些日志到原表上，以此达到数据的一致性。如果在 Oline DDL 执行期间，DML操作产生了重复键条目错误（ERROR 1062 (23000): Duplicate entry），虽然不会直接影响DDL操作，但会在DDL执行完成最终应用DML时报错，导致DDL执行失败。官方认为该问题是一种限制，并不是Bug，所以目前为止还没有得到解决。
这类似于 InnoDB 中的外键约束检查的原理，其中约束必须在事务期间保持。

官方文档 Oline DDL 限制链接

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

5 解决方案

官方认为该问题是一种限制，并不是Bug。

我们的表不大，并且是演示数据，解决方法是重建表，导入数据。

当然常规解决办法是：

1、用工具执行pt-osc或者ghost；

2、改成ALGORITHM=COPY，相当于没有用online ddl特性，跟5.6早期版本一样，创建临时表，拷贝数据，会有锁，阻塞dml（慎用）