* GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。
MySQL5.7.38主从架构,主节点唯一索引上(唯一索引不是主键)有重复值,全部从节点报1062,SQL线程状态异常,根据SQL线程报的binlog位置点,insert 数据时有重复值,插入失败
unique_checks=0时导致,在bug(106121)列表中官方解释的原因:该参数关闭,维护唯一索引时,不会进行物理读,只会进行内存读,来确保唯一索引的唯一性,即如果内存中有冲突数据就报1062,如果内存中没有冲突数据插入成功,不会进行io来将唯一索引相关的数据页拉取到内存。
官方的回复“IMHO this is not a bug”,我理解的意思“不要你觉得,我要我觉得,我就是这么玩的”。
一、临时解决方案
缺点是:不能够解决数据重复的问题,切换主从后会面临更多重复数据的问题,如果从节点接收查请求且使用到了原唯一索引的字段,那sql效率会严重下降,但是可以解决主从复制停止的问题
二、永久解决方案
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生成随机数据,插入表,并另起会话观察总数据量约10w条左右(保证聚簇索引中的前边的数据与后边的数据所在的叶子节点的页相差很远):
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))
查询前10条数据:
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
c与pad的值与id=1360值相等,id=1000000000(表中无该id行)
insert into wl.lgf(id,c,pad) values(10000000,'3344825394389018','7962994492618902') ;
目的是清除缓存 为了清空MySQL缓存容,还可结合以下几个参数 修改my.cnf文件,重启MySQL实例
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)
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)