mysql（Error_code: 1032）原因与通用修复方案

碰到MYSQL常见的1032错误，从库复制异常。

通过show slave status\G 命令也能看到对应的报错（处理时忘记截图了）。

通过报错我们可以去主库找到binlog中具体的报错。

mysqlbinlog --no-defaults -v -v --base64-output=DECODE-ROWS mysql-bin.018563 > 1106.sql

这是一个事务对应一堆delete操作，很难找到是那条记录错误导致的。（本来的想法是定位到具体的行，然后进行对应调整，让复制正常走下去）

之前有datetime字段数据不一致导致复制异常，其他字段完全正常，更新日期即可（并且让业务维护对应的主键）。

 

只能换个通用的方式进行修复了。

1.从库：stop slave;

2.主库导出该表一致性数据：mysqldump -uroot -p'xxxx' -h10.111.229.23  --single-transaction --master-data=2 --flush-logs bi im_bi_i_scrb > 1106.sql

3.从库导入数据：source 1106.sql

4.从库设置过滤，过滤该表：CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=('bi.im_bi_i_scrb');

5.从库启动复制，并设置结束位置（这个具体位置在导出文件1106.sql）：start slave until MASTER_LOG_FILE='mysql-bin.018568', MASTER_LOG_POS=316;

6.观察show slave status\G状态

复制到达指定位置slave-sql线程停止，变为NO；

或者观察gtid查看是否到导出文件那个位置。

7.停止复制：stop slave;

8.取消过滤：CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE=();

9.启动复制：start slave;

修复完毕！！！

我们看看官方怎么解释报错的原因和解决方案。

Applies to:

MySQL Server - Version 8.0 and later
Information in this document applies to any platform.

Cause

 When Row-based Logging (RBL) is used, a single UPDATE or DELETE statement on the primary can result in many binary log events in the binary log. Each such event needs to be mapped to the specific row in the table so that the event can be applied on the replica server.
If the table has a primary key or a unique key, the event is mapped to the corresponding row using the primary key or the unique key.
When the table does neither have a primary key nor a unique key, a hash function is used to map the event to the corresponding record.

On the replica, when it applies the event which has been mapped using a hash function, in some cases, it can result in replication error as shown above.

Solution

 The solution is to use invisible columns which is supported as of MySQL 8.0.23, as documented here:

https://dev.mysql.com/doc/refman/8.0/en/invisible-columns.html

The ALTER TABLE statement to add an invisible primary key is:

alter table emp add column empid int auto_increment primary key invisible;

 

In a SELECT * query, the * evaluates to all table columns, except those that are invisible.

So, the query "select * from emp;" returns the columns firstname, lastname and address;

Thus it does return the newly added primary key column "empid" and hence does NOT require any modification to the application.

 

其实就是要求你的表有主键，不然就会有1032的报错（BUG较多）！！！

mysql8.0.23以后支持不可见主键索引。