破防了,谁懂啊家人们:记一次MySQL问题排查

数据库 MySQL
一切的问题源自对create table as这个语句的不熟悉,这个语句建表导致的表主键、索引、auto_increment的丢失。

一、前言

简单介绍一下出问题的表。

一张元数据表,提取出重点部分,抽象出来的结构如下,

(id, group, code, name,property1, property2, ...)

id

group

code

name

property

1

业务1

事件1

吃冻干


2

业务1

事件2

喂猫粮


3

业务2

事件1

睡觉


4

业务3

事件10086

下班


...

...

...

...


主键primary key:id

唯一键unique key:group + code,

也就是说在该group内,code是唯一的。

此外,我们有一个dataworks离线任务,每天会往该表中写入记录,采用insert ignore into的方式,如果遇到重复的group+code,就不写入。

整体逻辑比较清晰明了。数据量级也比较小,每个group大约几百上千条数据,总数据量不到10w。

二、问题排查和修复过程

2.1 最初的问题

某天用户反馈线上产品报错,迅速排查发现,上述表中新接入了一个业务:在dataworks接入了一个新的group(假设名字叫bad_group),同步任务在当天异常往mysql表里导了千万量级数据(其中实际有效的只有几千条,其余为脏数据),导致线上产品查询缓慢、报错。定位到问题以后,第一反应是把错误的bad_group的数据先全部清掉,保留其他group的数据,恢复上线查询,然后再慢慢想办法重新导入正确数据。

顺带一提,以下SQL执行等全程都使用弹内DMS平台进行操作。

2.2 初步思路

清理错误数据v1

DELETE FROM MY_TABLE 
WHERE group = 'bad_group';

直接执行上面这个SQL进行普通数据变更可行吗?显示不行,有经验的同学都知道,在千万量级下,清理大量数据会超过binlog限制,导致SQL无法被执行。

因此我们直接用的是另一个方案,无锁数据变更,SQL依旧和上面保持一致,关于无锁变更的描述可见平台的介绍:

图片图片

本以为用无锁变更差不多就能解决问题了,然而执行过程中发现由于数据量比较大,无锁变更分批执行SQL效率非常低,估算大概要2h以上来清空这几千万的脏数据,不能接受这个方案,执行了几分钟果断放弃。

2.3 另辟蹊径

于是只能换一种方式。重新考虑这个问题,我们需要保留的数据仅仅只有千万中的不到10万条非bad_group的数据,因此除了删除bad_group数据这种方法,更简单的是将有效数据先copy到一张临时表中,然后drop原表,再重新创建表,将临时表中数据拷贝回来。为什么drop表会比delete数据快呢,这也是一个重要知识点。


DROP

TRUNCATE

DELETE

删除内容

删除整张表数据,表结构以及表的索引、约束和触发器

删除全部数据

删除部分数据(可带where条件)

语句类型

DDL

DDL

DML

效率

最高

较高

较低

回滚

无法回滚

无法回滚

可以回滚

自增值

-

重置

不重置

举个不那么恰当的例子,好比房东把房子租给别人,到期后发现房子里全都是垃圾,DELETE语句是将这些垃圾一件一件清理出来,只保留原来干净的家具。TRUNCATE相当于一把火把房子里所有东西都烧了,DROP语句就是房子直接不要了。

这里drop和truncate的方案都可以选择,我们采用了房子不要了的方案,直接drop表:

清理错误数据v2

-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';


-- 删除原表
DROP TABLE MY_TABLE;


-- 将临时表重命名为原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;

执行成功后,count(*)了一把数据量级,发现确实回到正常水准,于是问题就那么初步解决了。然而如果问题那么容易就解决了,那就不会记录在ATA。上面的SQL留下了一个巨坑,有经验的同学可能一眼就看出来了😭😭😭,如果没有看出来的话,继续下文。

2.4 表坏了

当天一切正常。然而好景不长,第二天,有同学往表里导数时发现了问题,在没有指定id的情况下,灌入的所有行id=0。我一脸黑人问号?

id不是默认主键吗,怎么会这样,重新打开表结构一看,所有的索引都消失了!

此时心里凉了半截,马上回想到一定是这个语句有问题:

-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';

赶紧问了下GPT:

图片图片

图片图片

果不其然,create table as 只会复制表的列信息结构和数据,不会复制表索引、主键等信息。

也就是说,这张表已经被玩坏了!现在回看这个问题,当时至少有两种方式避免这个问题:

  • 不使用drop语句。使用truncate语句,保留原表结构。

清理错误数据v3

-- 将正常数据复制到临时表
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE group <> 'bad_group';


-- 清空原表数据,但不删除表
TRUNCATE TABLE MY_TABLE;


-- 将临时表数据插入到原表
INSERT INTO MY_TABLE SELECT * FROM TEMP_TABLE;
  • 使用CREATE TABLE LIKE 语句创建临时表,复制原表结构。

清理错误数据v4

-- 创建和原表结构一样的临时表
CREATE TABLE TEMP_TABLE LIKE MY_TABLE;


-- 将正常数据复制到临时表
INSERT INTO TEMP_TABLE SELECT * FROM MY_TABLE WHERE group <> 'bad_group';


-- 删除原表
DROP TABLE MY_TABLE;


-- 将临时表重命名为原表
RENAME TABLE TEMP_TABLE TO MY_TABLE;

2.5 我觉得还能抢救一下

情况就是这么个情况,只能看看怎么抢救!

id

group

code

name

property

1

业务1

事件1

吃冻干


2

业务1

事件2

喂猫粮


3

业务2

事件1

睡觉


4

业务3

事件10086

下班


...

...

...

...


0(新导入)

业务1(重复数据)

事件1(重复数据)

吃冻干


0(新导入)

业务1(重复数据)

事件2(重复数据)

喂猫粮


0(新导入)

业务1

事件3

吃罐头


...

...

...

...


主键缺失导致插入了许多条id为0的数据,但应用不依赖mysql的自增id,暂时不影响线上应用查询结果;group+code的unique key缺失导致可能插入了重复数据,但应用侧做了去重兜底逻辑。也就是说不幸中的万幸,产品侧暂时无感,赶紧想办法挽回。

该表同步数据的方式是:如果唯一键冲突则忽略,否则就导入成功。新导入的这批数据由于缺失主键和唯一键,id全部为0且有重复,但其实只有一部分是需要保留的,另一部分需要根据唯一键去重。

此时我需要完成两件事:

  1. 保留原有数据的同时,将表的主键、唯一键和查询索引进行重建。
  2. 将今天新导入的id=0的数据根据原唯一键的规则重新导入。

但我们知道,执行添加唯一键的语句时,会检查此时表里是否有不满足唯一的数据,如果有的话该语句会被拒绝执行。因此这批带有重复的新数据的干扰,不能直接alter table add unique key。

灵机一动,采取和昨日一样的临时表方案,即先将id=0的数据复制到临时表,删除原表中所有id=0的数据,然后重建索引,再将id=0的数据使用insert ignore into语句导回来。对应的SQL:

重建表

-- 1.复制id=0的数据到临时表,
CREATE TABLE TEMP_TABLE AS SELECT * FROM MY_TABLE WHERE id = 0;


-- 2.删除源表中id=0的记录
DELETE FROM MY_TABLE WHERE id = 0;


-- 3.重建索引
ALTER TABLE MY_TABLE ADD INDEX ...;


-- 4.导回id=0的新数据
INSERT IGNORE INTO MY_TABLE SELECT * FROM TEMP_TABLE;

仔细思考,这次使用CREATE TABLE AS是没有问题的,因为这张临时表并不重要。DELETE由于数据量不大也没有性能问题。出于谨慎,上述4个SQL也是通过4个工单一个个提交执行的,便于中间过程观察。思路清晰,这次应该ok!

当执行完上面第2条语句,删除id=0的数据后,执行了select count(*)简单确认了一下,没想到这一确认还真出了问题,delete过后数据条数没有变?!经过紧张的思考🤔,新机子哇伊自摸一刀子:猜测大概率是主备没有实时同步。关于这一点,我们线上用的MYSQL是主库,工单执行的SQL也是在主库执行,但DMS控制台为了不影响线上正常使用,是在备库进行查询,正常情况下主备库会实时同步。但当一些耗时SQL执行时,就会出现同步延迟。为了验证这一点,可以在主库select count(*),DMS也提供了切换选项,只是默认会选备库。

图片图片

这张截图是后来我咨询了DBA后帮忙查询到的结果,确实是有延迟。

图片图片

继续重建索引,包括主键primary key、唯一键unique key、普通索引key。没有问题。

最后一步,将id=0的数据从临时表导回原表,就可以回家喂🐱了,然而工单一直执行报错。

[ERROR] Duplicate entry '0' for key 'PRIMARY'【解决方法】:https://help.aliyun.com/document_detail/198139.html
TraceId : 0b8464d617047224212725080d867f

百思不得其解,按理想情况,重新导回数据后,id应该是从此刻的最大id开始自增才对(假设表中有10000条数据,那么新插入的数据理应id=10001),为什么还是0,并且还重复了?难道是之前的CREATE TABLE AS语句导致auto increment被清为0了?

按照这个思路,回忆起之前在日常环境写假数据的时候,如果指定了一个比较大的id,那么后续所有新数据都会在这个id基础上生成(比如当前表中只有10条记录,id=10,插入一条id=100的数据,后续数据就会接着id=101继续生成。)尝试过后发现依旧报错。

我有点汗流浃背了。

为什么不管用?又用GPT查询了设置表auto increment值的方法:

ALTER TABLE MY_TABLE AUTO_INCREMENT = 10001;

然而仍然报这个错误。

绝望。

此时已经夜里快十点,周围没有什么人了,本来空调澎湃吹动热气的声音也不知不觉趋于安静,我望向对面楼栋,灯光明灭可见。一月小寒的夜晚有些冷,我突然想起李清照的那句“冷冷清清,凄凄惨惨戚戚”,不就在描绘这个场景吗?

最后的最后,再次对比日常库的正常表结构,发现原来是id的auto increment也消失了。原来还是create table as 留下来的坑,难怪之前重新设置auto increment也不生效。为什么没有第一时间发现到这一点,因为按上面gpt的回答,该语句对"列结构"是可以正常复制的,只有索引、主键等信息会丢失,原以为"AUTO_INCREMENT"是属于id这一列的列信息,看起来并不是。

图片图片

重新设置id使用自增:

MODIFY COLUMN `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID';

至此问题解决。

三、总结

一切的问题源自对create table as这个语句的不熟悉,这个语句建表导致的表主键、索引、auto_increment的丢失。

不熟悉的SQL不能乱跑😭😭😭

后面也在反思在线上使用drop和truncate有些激进。不过当时考虑到是内部应用并且查询已经不可用了。也欢迎读者同学们思考和反馈,针对这样的场景是否有更好处理建议。 

顺便说明:后续我们针对odps导入mysql源头就做了限制,防止这类事情再次发生。

责任编辑:武晓燕 来源: JAVA日知录
相关推荐

2023-04-06 07:53:56

Redis连接问题K8s

2021-05-13 08:51:20

GC问题排查

2019-03-15 16:20:45

MySQL死锁排查命令

2017-12-19 14:00:16

数据库MySQL死锁排查

2021-03-29 12:35:04

Kubernetes环境TCP

2021-11-23 21:21:07

线上排查服务

2022-02-08 17:17:27

内存泄漏排查

2020-06-12 13:26:03

线程池故障日志

2023-01-04 18:32:31

线上服务代码

2021-04-13 08:54:28

dubbo线程池事故排查

2022-11-03 16:10:29

groovyfullGC

2023-04-13 12:00:00

MySQLSQL线程

2020-08-12 08:25:43

数据库MySQL技术

2021-08-13 13:55:13

网络安全勒索软件互联网

2023-01-05 11:44:43

性能HTTPS

2022-11-16 08:00:00

雪花算法原理

2018-02-23 13:41:05

数据库MySQL数据恢复

2018-01-19 11:12:11

HTTP问题排查

2019-09-10 10:31:10

JVM排查解决

2011-08-12 09:30:02

MongoDB
点赞
收藏

51CTO技术栈公众号