MySQL危险而诡异的update操作和惊魂5分钟

来源:转载


MySQL危险而诡异的update操作和惊魂5分钟简介

Part1:写在最前

上班正忙的不可开交呢,一个消息过来,得知研发人员误操作数据库了....不带where条件,整表更新Orz,还让不让人好好活了,心中一万只XXX啊~无奈,分清事情的轻重,优先处理这起事故。

在简单沟通后,了解到事故的原因是研发人员使用update忘记带where条件。这本身没什么诡异的,诡异的是在决定要不要进行恢复的时候,笔者稍微犹豫了以下,因为看起来是不需要恢复的,那么具体是什么样的情况呢?

Part2:危险场景再现

研发人员update使用了错误的语法,本意是update helei3 set a='1' where b='a';

结果写成了update helei3 set a='1' and b='a';

这样的话使得helei3这张表的a列被批量修改为0或1。

过了几秒钟,发现写错并且已经敲了回车后,此时update语句还没有更新完,立即ctrl+c

那么数据到底有没有被写脏?

复现

Part1:创建所需表

首先我们创建测试表,a列b列均为varchar类型

[email protected](helei)>showcreatetablehelei3/G
***************************1.row***************************
Table:helei3
CreateTable:CREATETABLE`helei3`(
`a`varchar(10)DEFAULTNULL,
`b`varchar(255)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

表中数据如下

[email protected](helei)>select*fromhelei3;
+------+------+
|a|b|
+------+------+
|1|a|
|2|b|
|3|c|
+------+------+
3rowsinset(0.00sec)

Part2:错误语句生成

我们都知道,update的语法是update tablename set col1=val,col2=val2 where xxx;

那么当逗号换成了and,会出现什么样的严重后果呢?

这个时候由于没有where条件,导致整表更新,那猜猜看后续结果是什么

[email protected](helei)>updatehelei3seta='1'andb='a';
[email protected](helei)>select*fromhelei3;
+------+------+
|a|b|
+------+------+
|1|a|
|0|b|
|0|c|
+------+------+
4rowsinset(0.00sec)

没错,这个SQL将a列整表更新为0,而之所以第一个a=1是由于a='1' and b='a'这个条件是真,所以为1。

Part3:ctrl+c

了解Part2后,我们再看下当update全表更新发现误操作后立即ctrl+c能不能回滚避免误操作。

提前准备好一张50万数据的表

[email protected](helei)>selectcount(*)fromhelei;
+----------+
|count(*)|
+----------+
|500000|
+----------+
1rowinset(0.06sec)
[email protected](helei)>showcreatetablehelei/G
***************************1.row***************************
Table:helei
CreateTable:CREATETABLE`helei`(
`id`int(10)unsignedNOTNULLAUTO_INCREMENT,
`c1`int(10)NOTNULLDEFAULT'0',
`c2`int(10)unsignedDEFAULTNULL,
`c5`int(10)unsignedNOTNULLDEFAULT'0',
`c3`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
`c4`varchar(200)NOTNULLDEFAULT'',
PRIMARYKEY(`id`),
KEY`idx_c1`(`c1`),
KEY`idx_c2`(`c2`)
)ENGINE=InnoDBAUTO_INCREMENT=500001DEFAULTCHARSET=utf8mb4
1rowinset(0.00sec)

误操作整表更新后等待几秒立即ctrl + c

[email protected](helei)>updateheleisetc2=1;
^CCtrl-C--sending"KILLQUERY2133"toserver...
Ctrl-C--queryaborted.
^CCtrl-C--sending"KILL2133"toserver...
Ctrl-C--queryaborted.
ERROR2013(HY000):LostconnectiontoMySQLserverduringquery
[email protected](helei)>select*fromheleiwherec2=1;
Emptyset(0.00sec)

可以看到c2列并没有出现部分更新为1的情况,也就是说整表更新的这条操作回滚了。

细心点可以看到binlog pos号也没有发生变化

[email protected](helei)>showmasterstatus;
+------------------+-----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+-----------+--------------+------------------+
|mysql-bin.000004|124886658|||
+------------------+-----------+--------------+------------------+
1rowinset(0.00sec)

Part4:诡异

前三章看完后,我们来看下有什么地方是诡异的,在生产环境中,由于不知道刚刚那条SQL是否已经更新了部分数据,我们采取了这种方式来验证。

[email protected](helei)>select*fromhelei3wherea='0';
+------+------+
|a|b|
+------+------+
|0|b|
|0|c|
+------+------+
2rowsinset(0.00sec)

[email protected](helei)>select*fromhelei3wherea=0;
+------+------+
|a|b|
+------+------+
|0|b|
|0|c|
|zz|zz|
+------+------+
3rowsinset(0.00sec)

发现数据不一致,生产环境的更唬人一些,列中并没有存储0,而都是字母或纯数字,当我执行上述两个SQL的时候,发现结果差了非常多,还爆出了很多的warnings。

| Warning | 1292 | Truncated incorrect DOUBLE value: 'XXX' |

那么我想知道刚刚的误操作到底是不是生效了呢,为什么会出现差个引号结果就差这么多呢?

分析

Part1:构建数据

[email protected](helei)>insertintohelei3values('zz','zz');

[email protected](helei)>select*fromhelei3;
+------+------+
|a|b|
+------+------+
|1|a|
|0|b|
|0|c|
|zz|zz|
+------+------+
4rowsinset(0.00sec)

Part2:查询对比

那么这时我们执行一条查询会有两种结果

[email protected](helei)>select*fromhelei3wherea='0';
+------+------+
|a|b|
+------+------+
|0|b|
|0|c|
+------+------+
2rowsinset(0.00sec)

[email protected](helei)>select*fromhelei3wherea=0;
+------+------+
|a|b|
+------+------+
|0|b|
|0|c|
|zz|zz|
+------+------+
3rowsinset(0.00sec)

这是为什么呢?

Part3:root cause

[email protected](helei)>select'zz'=0;
+--------+
|'zz'=0|
+--------+
|1|
+--------+
1rowinset,1warning(0.00sec)

[email protected](helei)>select'zz3'=0;
+---------+
|'zz3'=0|
+---------+
|1|
+---------+
1rowinset,1warning(0.00sec)

[email protected](helei)>select'3'=0;
+-------+
|'3'=0|
+-------+
|0|
+-------+
1rowinset(0.00sec)

可以看出,当包含字母的时候,mysql认为=0是真,并抛出warning。

[email protected](helei)>showwarnings;
+---------+------+----------------------------------------+
|Level|Code|Message|
+---------+------+----------------------------------------+
|Warning|1292|TruncatedincorrectDOUBLEvalue:'zz'|
+---------+------+----------------------------------------+
1rowinset(0.00sec)

Part4:MySQL Doc

InInnoDB, all user activity occurs inside a transaction. Ifautocommitmode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection withautocommitenabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.

Part5:我的理解

InnoDB存储引擎符合事务的ACID特性。 它将一次完成所有操作,或者在中断时不会执行操作和回滚。 InnoDB也是MySQL 5.5及以上版本的默认引擎。

但是对于非事务性的MyISAM存储引擎。 他的原子操作是一行一行完成的。 所以如果你中断这个过程,那就会更新/删除到哪里就到哪里了。

——总结——

通过本文,您能了解到update中使用了and这种错误语法带来的严重后果,以及在SQL语句执行完之前,ctrl +c 到底有没有效果~由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。


分享给朋友:
您可能感兴趣的文章:
随机阅读: