MySQL·捉虫动态·ALTERIGNORETABLE导致主备不一致

背景

我们知道当一张表的某个字段存在重复值时&＃xff0c;这个字段没办法直接加UNIQUE KEY&＃xff0c;但是MySQL提供了一个 ALTER IGNORE TABLE的方式&＃xff0c;可以忽略修改表结构过程中出现的错误&＃xff0c;但是要忽略UNIQUE重复值&＃xff0c;就需要打开old_alter_table&＃xff0c;也就是拷贝表的方式来ALTER TABLE。

例如这样&＃xff1a;

CREATE TABLE t1(c1 int) ENGINE &＃61; InnoDB;
INSERT INTO t1 VALUES (1), (1);
SET old_alter_table&＃61;1;
ALTER IGNORE TABLE t1 ADD UNIQUE (c1);


但是如果你是 MySQL 5.5 主备环境&＃xff0c;你会发现备库收到这个DDL后&＃xff0c;SQL THREAD 会给你一个无情的报错&＃xff1a;

&＃39;Error &＃39;Duplicate entry &＃39;1&＃39; for key &＃39;c1&＃39;&＃39; on query.
Default database: &＃39;test&＃39;. Query: &＃39;ALTER IGNORE TABLE t1 ADD UNIQUE (c1)&＃39;&＃39;


原因

这是为什么呢&＃xff1f;

其实关键问题就是这个SQL要执行成功&＃xff0c;必须保证 old_alter_table 打开&＃xff0c;但是 MySQL 的 SET 语句并不参与复制&＃xff0c;因此备库只收到了一个 ALTER IGNORE TABLE&＃xff0c;而没有先打开 old_alter_table&＃xff0c;因此备库用的不是整表拷贝的方法来重建表&＃xff0c;因而无法执行成功。

解决

那我们怎么解决这个问题呢&＃xff0c;也很简单&＃xff0c;只要备库Slave线程也用 old_alter_table&＃61;1 来执行 ALTER IGNORE TABLE就好了。

本质上就是 mysql_alter_table() 中需要让need_copy_table&＃61; ALTER_TABLE_DATA_CHANGED&＃xff08;old_alter_table&＃61;1&＃xff09;&＃xff0c;而不是need_copy_table&＃61; ALTER_TABLE_INDEX_CHANGED&＃xff08;old_alter_table&＃61;0&＃xff09;。

因此我们只要在mysql_alter_table()函数中判断该用哪种算法的时候&＃xff0c;给出一个可以干预的变量&＃xff0c;让Slave线程在需要的时候可以按need_copy_table&＃61; ALTER_TABLE_DATA_CHANGED执行。

原来的代码&＃xff1a;

if ((thd->variables.old_alter_table|| (table->s->db_type() !&＃61; create_info->db_type)#ifdef WITH_PARTITION_STORAGE_ENGINE|| partition_changed#endif)need_copy_table&＃61; ALTER_TABLE_DATA_CHANGED;

我们加上判断 (‘是否启用Slave自动用 ALTER_TABLE_DATA_CHANGED 方式做ALTER IGNORE TABLE’ && thd->slave_thread && ignore)&＃xff0c;就可以在我们打开控制变量的时候&＃xff0c;强制让Slave线程用 old_alter_table&＃61;1 的方式来执行 ALTER IGNORE TABLE。

if ((thd->variables.old_alter_table ||(&＃39;是否启用Slave自动用 ALTER_TABLE_DATA_CHANGED 方式做ALTER IGNORE TABLE&＃39; &&thd->slave_thread && ignore))|| (table->s->db_type() !&＃61; create_info->db_type)#ifdef WITH_PARTITION_STORAGE_ENGINE|| partition_changed#endif)need_copy_table&＃61; ALTER_TABLE_DATA_CHANGED;