unique_checks=0時(shí)導(dǎo)致，在bug(106121)列表中官方解釋的原因：該參數(shù)關(guān)閉，維護(hù)唯一索引時(shí)，不會(huì)進(jìn)行物理讀，只會(huì)進(jìn)行內(nèi)存讀，來(lái)確保唯一索引的唯一性，即如果內(nèi)存中有沖突數(shù)據(jù)就報(bào)1062，如果內(nèi)存中沒(méi)有沖突數(shù)據(jù)插入成功，不會(huì)進(jìn)行io來(lái)將唯一索引相關(guān)的數(shù)據(jù)頁(yè)拉取到內(nèi)存。
一、問(wèn)題：
MySQL5.7.38主從架構(gòu)，主節(jié)點(diǎn)唯一索引上（唯一索引不是主鍵）有重復(fù)值，全部從節(jié)點(diǎn)報(bào)1062，SQL線程狀態(tài)異常，根據(jù)SQL線程報(bào)的binlog位置點(diǎn)，insert 數(shù)據(jù)時(shí)有重復(fù)值，插入失敗
二、原因：
unique_checks=0時(shí)導(dǎo)致，在bug(106121)列表中官方解釋的原因：該參數(shù)關(guān)閉，維護(hù)唯一索引時(shí)，不會(huì)進(jìn)行物理讀，只會(huì)進(jìn)行內(nèi)存讀，來(lái)確保唯一索引的唯一性，即如果內(nèi)存中有沖突數(shù)據(jù)就報(bào)1062，如果內(nèi)存中沒(méi)有沖突數(shù)據(jù)插入成功，不會(huì)進(jìn)行io來(lái)將唯一索引相關(guān)的數(shù)據(jù)頁(yè)拉取到內(nèi)存。
官方的回復(fù)“IMHO this is not a bug”，我理解的意思“不要你覺(jué)得，我要我覺(jué)得，我就是這么玩的”。
三、故障解決方案：
1.臨時(shí)解決方案
在從節(jié)點(diǎn)開(kāi)啟會(huì)話(huà)
set sql_log_bin=0
刪除表的唯一索引
重新啟動(dòng)復(fù)制線程
缺點(diǎn)是：不能夠解決數(shù)據(jù)重復(fù)的問(wèn)題，切換主從后會(huì)面臨更多重復(fù)數(shù)據(jù)的問(wèn)題，如果從節(jié)點(diǎn)接收查請(qǐng)求且使用到了原唯一索引的字段，那sql效率會(huì)嚴(yán)重下降，但是可以解決主從復(fù)制停止的問(wèn)題
2.永久解決方案
- 業(yè)務(wù)自己去重，不要插入重復(fù)數(shù)據(jù)
- 參數(shù)unique_checks保持為1
- 關(guān)于重復(fù)的業(yè)務(wù)數(shù)據(jù)：與業(yè)務(wù)交流，確定重復(fù)數(shù)據(jù)的處理方式
四、復(fù)現(xiàn)步驟：
1. 表結(jié)構(gòu)：
mysql> create database wl;
mysql> show create table wl.lgf\G
*************************** 1. row ***************************
Table: lgf
Create Table: CREATE TABLE `lgf` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `c` (`c`,`pad`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8
Python生成隨機(jī)數(shù)據(jù)，插入表,并另起會(huì)話(huà)觀察總數(shù)據(jù)量約10w條左右（保證聚簇索引中的前邊的數(shù)據(jù)與后邊的數(shù)據(jù)所在的葉子節(jié)點(diǎn)的頁(yè)相差很遠(yuǎn)）：
rand.py
import random
import os
while True:
i=str(random.randint(1000,8000000))
a=str(random.randint(1000000000000000,8000000000000000))
b=str(random.randint(1000000000000000,8000000000000000))
c=str(random.randint(100000,800000))
sql="insert ignore into lgf(id,k,c,pad) values(%s,%s,%s,%s) " % (i,c,a,b)
os.system('mysql -uroot -p123456 -h127.0.0.1 -P3306 -e "use wl;%s"' % (sql))
2. 查詢(xún)數(shù)據(jù)：
查詢(xún)前10條數(shù)據(jù)：
mysql> select * from wl.lgf order by id limit 10;
+------+--------+------------------+------------------+
| id | k | c | pad |
+------+--------+------------------+------------------+
| 1058 | 162327 | 1693367460515515 | 4503256156555111 |
| 1072 | 581438 | 7079984640802065 | 3180334749170868 |
| 1139 | 160022 | 5072986485096872 | 4163430310554381 |
| 1193 | 780611 | 4790797228737408 | 2940698105313885 |
| 1234 | 395757 | 4904177529354516 | 4353197763651083 |
| 1243 | 725513 | 5525166443023382 | 5731401212245669 |
| 1262 | 749163 | 1132694876665847 | 5159069792931202 |
| 1280 | 415220 | 2770815803363126 | 3979264947141008 |
| 1316 | 329253 | 6088415865037450 | 6035685143204331 |
| 1360 | 403078 | 3344825394389018 | 7962994492618902 |
+------+--------+------------------+------------------+
10 rows in set (0.00 sec)
id=1360 c=3344825394389018 pad=7962994492618902
3. 拼接SQL
c與pad的值與id=1360值相等，id=1000000000（表中無(wú)該id行）
insert into wl.lgf(id,c,pad) values(10000000,'3344825394389018','7962994492618902') ;
4. 重啟mysqld
目的是清除緩存 為了清空MySQL緩存容，還可結(jié)合以下幾個(gè)參數(shù) 修改my.cnf文件，重啟MySQL實(shí)例
- innodb_buffer_pool_load_at_startup = 0
- innodb_buffer_pool_dump_at_shutdown = 0
5. 重新插入重復(fù)唯一索引數(shù)據(jù)：
mysql> set unique_checks=0;
mysql> use wl
mysql> insert into wl.lgf(id,c,pad) values(10000000,'3344825394389018','7962994492618902') ;
Query OK, 1 row affected (0.00 sec)
6. 查詢(xún)：force index指定主鍵查詢(xún)數(shù)據(jù)
mysql> select * from wl.lgf force index(primary) where c='3344825394389018' and pad='7962994492618902';
+----------+--------+------------------+------------------+
| id | k | c | pad |
+----------+--------+------------------+------------------+
| 1360 | 403078 | 3344825394389018 | 7962994492618902 |
| 10000000 | 0 | 3344825394389018 | 7962994492618902 |
+----------+--------+------------------+------------------+
2 rows in set (0.37 sec)
參考文檔
MySQL Bugs: #106121: Unique key constraint invalid(https://bugs.mysql.com/bug.php?id=106121)
MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables(https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_unique_checks)
