乐趣区

关于数据库:故障案例MySQL唯一索引有重复值官方却说This-is-not-a-bug

jiezi
  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
  • GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。
  • 作者：飞鱼过天
  • 文章起源：GreatSQL 社区原创
  • 问题
  • 起因
  • 故障解决方案
  • 复现步骤
  • 参考文献

一、问题：

MySQL5.7.38 主从架构，主节点惟一索引上（惟一索引不是主键）有反复值，全副从节点报 1062，SQL 线程状态异样，依据 SQL 线程报的 binlog 地位点，insert 数据时有反复值，插入失败

二、起因：

unique_checks= 0 时导致，在 bug(106121)列表中官网解释的起因：该参数敞开，保护惟一索引时，不会进行物理读，只会进行内存读，来确保惟一索引的唯一性，即如果内存中有抵触数据就报 1062，如果内存中没有抵触数据插入胜利，不会进行 io 来将惟一索引相干的数据页拉取到内存。

官网的回复“IMHO this is not a bug”，我了解的意思“不要你感觉，我要我感觉，我就是这么玩的”。

三、故障解决方案：

一、长期解决方案

  • 复原主从：

    • 在从节点开启会话
    • set sql_log_bin=0
    • 删除表的惟一索引
    • 重新启动复制线程

毛病是：不可能解决数据反复的问题，切换主从后会面临更多反复数据的问题，如果从节点接管查申请且应用到了原惟一索引的字段，那 sql 效率会重大降落，然而能够解决主从复制进行的问题

二、永恒解决方案

  1. 业务本人去重，不要插入反复数据
  2. 参数 unique_checks 放弃为 1
  3. 对于反复的业务数据：与业务交换，确定反复数据的解决形式

四、复现步骤：

1. 表构造：

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))

2. 查问数据：

查问前 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

3. 拼接 SQL

c 与 pad 的值与 id=1360 值相等，id=1000000000（表中无该 id 行）

insert into wl.lgf(id,c,pad) values(10000000,'3344825394389018','7962994492618902') ;

4. 重启 mysqld

目标是革除缓存 为了清空 MySQL 缓存容，还可联合以下几个参数 批改 my.cnf 文件，重启 MySQL 实例

  • innodb_buffer_pool_load_at_startup = 0
  • innodb_buffer_pool_dump_at_shutdown = 0

5. 从新插入反复惟一索引数据：

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. 查问：force index 指定主键查问数据

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.ph…)
MySQL :: MySQL 8.0 Reference Manual :: 5.1.8 Server System Variables(https://dev.mysql.com/doc/ref…)

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-10…

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

退出移动版