MySQL问题两则

1 | 前言

近期在给开发小伙伴解决问题的时候，收集了两个个人觉得比较有意思的问题给大家分享一下。一个是在执行ALTER TABLE ADD COLUMN语句时，报了Duplicate entry的错误；另一个是关于在MySQL中正确存取emoji表情的问题。

2 | ALTER TABLE ADD COLUMN报Duplicate entry错误

2.1 问题描述

某日系统上线，接到开发小伙伴电话说在上线时，执行一个增加字段的DDL语句脚本时，报错了，错误如下:
ERROR 1062 (23000) at line 1: Duplicate entry 'UR000021426347' for key 'T_CAP_CUST_MIDDLE_INFO_UNIQ_INDEX' 
根据错误提示的条件去数据库中查询却只能查到一条记录，并没有重复记录。DDL脚本无法执行，影响后续上线步骤了。当时由于不在现场，了解到的信息只有：

1. DDL语句脚本中只有两条DDL语句，且都是添加字段的语句。

2. 脚本正常运行只需要40-70秒。

3. 当时并不是停业窗口。

考虑到数据库版本为5.6.34，添加字段并不会阻塞DML操作，让开发小伙伴再运行一次试试，结果这次执行成功了，并没有报冲突的错误。线上问题顺利解决，具体原因得线下分析了。虽说解决问题是主要矛盾，但是搞清楚问题原因有着更深层次的意义。

2.2 原因定位

下面就是到了寻找问题原因的时候了，为什么同样的DDL语句脚本第一次执行的时候报了Duplicate entry错误，第二次却顺利运行了。其实问题原因很好找，打开Google，输入关键字mysql alter table add column duplicate entry，搜索结果中很多关键字完全匹配的链接，说明很多人遇到过相同问题。搜索结果中一眼就看到一个链接MySQL Bugs:#76895:Adding new column OR Drop column causes duplicate PK error，看到MySQL Bug就莫名兴奋。通过该Bug链接了解到该问题是Online DDL的一个限制问题，官方认为该问题是一种限制，并不是Bug，所以目前为止还没有得到解决。

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.
 解释一下就是当执行Oline DDL操作时，MySQL实际上是将DML缓存（该缓存大小由变量 innodb_online_alter_log_max_size控制，默认128M）起来，等DDL执行完成后再将缓存中的DML重新应用到表上。如果有别的线程执行了DML操作，在DDL完成后，应用DML时，可能会出现duplicate entry错误。

2.3 实验验证

上面通过Google找到了理论上可能能解释问题的原因描述，但是还没有实际验证，所以接下来就是线下复现环节。先去找开发同事问了下线上报错的表只有一种操作insert into ... on duplicate key...，且报Duplicate entry的字段上有唯一索引。如果没有冲突的记录则插入，否则就更新。那么验证测试步骤也比较简单了，找一张测试表，执行ALTER TABLE ADD COLUMN操作，并同时执行insert into...on duplicate key...操作，观察DDL语句是否会有报错。

2.3.1 实验环境

• redhat-6.7/redhat-7.4

• MySQL-5.6.34/MySQL-5.7.22

2.3.2 操作步骤

• 准备测试环境。