背景

在阿里云DMS上执行了一个DDL语句，本意是将value1和value2增加联合唯一索引，并将value1的字段从可空表更为不可空。

1
2

ALTER TABLE TABLE ADD CONSTRAINT uk_value1_value2 UNIQUE KEY (value1,value2);
ALTER TABLE TABLE MODIFY COLUMN value1 varchar(20) NOT NULL;

执行之后导致原表丢失上千条数据，突然比较好奇，为啥ALTER语句会造成数据删除呢？

复现

初始准备

1
2
3
4
5
6
7
8
9

CREATE TABLE test (
	id bigint auto_increment NOT NULL,
	value1 varchar(20) NULL,
	value2 varchar(20) NULL,
	CONSTRAINT primaryKey PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;

再初始化几条测试数据

1
2
3
4
5
6

INSERT IGNORE INTO test (value1,value2) values(null,'张三');
INSERT IGNORE INTO test (value1,value2) values(null,'张三');
INSERT IGNORE INTO test (value1,value2) values(null,'张三');
INSERT IGNORE INTO test (value1,value2) values(null,'李四');
INSERT IGNORE INTO test (value1,value2) values(null,'王五');

使用sql脚本进行变更

1
2

ALTER TABLE test ADD CONSTRAINT uk_value1_value2 UNIQUE KEY (value1,value2);
ALTER TABLE test MODIFY COLUMN value1 varchar(20) NOT NULL;

可以发现第一个ALTER语句顺利执行，联合唯一索引中的一个字段值为NULL，其余值相同不会被认为是重复数据。

第二个ALTER语句执行失败，因为设置了NOT NULL，但是没有设置DEFAULT 值，原数据中存在NULL值，所以执行失败了。

使用DMS工单来执行

这里采用sql语句来模拟DMS工单执行

1
2
3
4
5
6
7
8

CREATE TABLE tmp_table_name LIKE test;

ALTER TABLE tmp_table_name ADD CONSTRAINT uk_value1_value2 UNIQUE KEY (value1,value2);
ALTER TABLE tmp_table_name MODIFY COLUMN value1 varchar(20) NOT NULL;

INSERT IGNORE INTO tmp_table_name (SELECT id,valu1,value2 FROM test LOCK IN SHARE MODE);

select * from tmp_table_name;

发现sql顺利执行完成，但表中的数据丢失了2条。

通过SQL客户端工具查看信息，能得到一些告警

原因是唯一索引冲突了，并且被忽略了。

原因

DMS执行逻辑

DMS是先创建一个临时表，再将ALTER语句执行到临时表上，接着使用INSERT IGNORE INTO 复制原表数据。
再接着使用Binlog增量同步到新表上，最后rename一下表名，替换原表。

这和我们直接修改执行ALTER有些差异：

1. 由于是空表，ALTER语句并不会报错（例如非空、唯一索引约束等等）
2. 由于使用了INSERT IGNORE INTO ，导致null值被错误的赋值了默认值（表结构中没有default值定义），导致唯一索引冲突，并忽略了错误。

疑问

1. 按照这个流程，如果原表存在重复数据，也能被加上唯一索引，只是会丢数据。事实却相反，DMS会报错。

参考DMS无锁变更说明，发现唯一索引被用于了全量表分段操作和增量更新，这可能是执行新增唯一索引会报错的原因。

目标表中有主键或唯一键：在执行无锁结构变更时，主键或唯一键用于全量拷表分段操作及后续增量更新。
说明
如果目标表中仅有主键或唯一键，在执行结构变更期间更新主键或唯一键，将导致任务执行失败。

2. 为什么INSERT IGNORE INTO会把NULL值复制到NOT NULL字段上时默认设置一个未指定的默认值？

可以参考mysql官方文档

Data conversions that would trigger errors abort the statement if IGNORE is not specified. With IGNORE, invalid values are adjusted to the closest values and inserted; warnings are produced but the statement does not abort. You can determine with the mysql_info() C API function how many rows were actually inserted into the table.

如果未指定IGNORE，会触发错误的数据转换将终止语句。使用IGNORE时，无效值会被调整为最接近的值并插入；会产生警告，但语句不会终止。你可以使用mysql_info() C API 函数来确定实际插入到表中的行数。

官方文档中也给出了示例