共计 5486 个字符,预计需要花费 14 分钟才能阅读完成。
简介
Part1: 写在最前
上班正忙的不可开交呢,一个消息过来,得知研发人员误操作 MySQL 数据库了 …. 不带 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 类型
root@127.0.0.1 (helei)> show create table helei3\G
*************************** 1. row ***************************
Table: helei3
Create Table: CREATE TABLE `helei3` (
`a` varchar(10) DEFAULT NULL,
`b` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
表中数据如下
root@127.0.0.1 (helei)>
select
* from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows
in
set
(0.00 sec)
Part2: 错误语句生成
我们都知道,update 的语法是 update tablename set col1=val,col2=val2 where xxx;
那么当逗号换成了 and,会出现什么样的严重后果呢?
这个时候由于没有 where 条件,导致整表更新,那猜猜看后续结果是什么
root@127.0.0.1 (helei)> update helei3
set
a=
'1'
and b=
'a'
;
root@127.0.0.1 (helei)>
select
* from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 0 | b |
| 0 | c |
+------+------+
4 rows
in
set
(0.00 sec)
没错,这个 SQL 将 a 列整表更新为 0,而之所以第一个 a = 1 是由于 a =’1′ and b=’a’ 这个条件是真,所以为 1。
Part3:ctrl+c
了解 Part2 后,我们再看下当 update 全表更新发现误操作后立即 ctrl+ c 能不能回滚避免误操作。
提前准备好一张 50 万数据的表
root@127.0.0.1 (helei)>
select
count(*) from helei;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row
in
set
(0.06 sec)
root@127.0.0.1 (helei)> show create table helei\G
*************************** 1. row ***************************
Table: helei
Create Table: CREATE TABLE `helei` (
`
id
` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(10) NOT NULL DEFAULT
'0'
,
`c2` int(10) unsigned DEFAULT NULL,
`c5` int(10) unsigned NOT NULL DEFAULT
'0'
,
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT
''
,
PRIMARY KEY (`
id
`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4
1 row
in
set
(0.00 sec)
误操作整表更新后等待几秒立即 ctrl + c
root@127.0.0.1 (helei)> update helei
set
c2=1;
^CCtrl-C -- sending
"KILL QUERY 2133"
to server ...
Ctrl-C -- query aborted.
^CCtrl-C -- sending
"KILL 2133"
to server ...
Ctrl-C -- query aborted.
ERROR 2013 (HY000): Lost connection to MySQL server during query
root@127.0.0.1 (helei)>
select
* from helei where c2=1;
Empty
set
(0.00 sec)
可以看到 c2 列并没有出现部分更新为 1 的情况,也就是说整表更新的这条操作回滚了。
细心点可以看到 binlog pos 号也没有发生变化
root@127.0.0.1 (helei)> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000004 | 124886658 | | |
+------------------+-----------+--------------+------------------+
1 row
in
set
(0.00 sec)
Part4: 诡异
前三章看完后,我们来看下有什么地方是诡异的,在生产环境中,由于不知道刚刚那条 SQL 是否已经更新了部分数据,我们采取了这种方式来验证。
root@127.0.0.1 (helei)>
select
* from helei3 where a=
'0'
;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
+------+------+
2 rows
in
set
(0.00 sec)
root@127.0.0.1 (helei)>
select
* from helei3 where a=0;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
3 rows
in
set
(0.00 sec)
发现数据不一致,生产环境的更唬人一些,列中并没有存储 0,而都是字母或纯数字,当我执行上述两个 SQL 的时候,发现结果差了非常多,还爆出了很多的 warnings。
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘XXX’ |
那么我想知道刚刚的误操作到底是不是生效了呢,为什么会出现差个引号结果就差这么多呢?
分析
Part1: 构建数据
root@127.0.0.1 (helei)> insert into helei3 values(
'zz'
,
'zz'
);
root@127.0.0.1 (helei)>
select
* from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
4 rows
in
set
(0.00 sec)
Part2: 查询对比
那么这时我们执行一条查询会有两种结果
root@127.0.0.1 (helei)>
select
* from helei3 where a=
'0'
;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
+------+------+
2 rows
in
set
(0.00 sec)
root@127.0.0.1 (helei)>
select
* from helei3 where a=0;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
3 rows
in
set
(0.00 sec)
这是为什么呢?
Part3:root cause
root@127.0.0.1 (helei)>
select
'zz'
=0;
+--------+
|
'zz'
=0 |
+--------+
| 1 |
+--------+
1 row
in
set
, 1 warning (0.00 sec)
root@127.0.0.1 (helei)>
select
'zz3'
=0;
+---------+
|
'zz3'
=0 |
+---------+
| 1 |
+---------+
1 row
in
set
, 1 warning (0.00 sec)
root@127.0.0.1 (helei)>
select
'3'
=0;
+-------+
|
'3'
=0 |
+-------+
| 0 |
+-------+
1 row
in
set
(0.00 sec)
可以看出,当包含字母的时候,mysql 认为 = 0 是真,并抛出 warning。
root@127.0.0.1 (helei)> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value:
'zz'
|
+---------+------+----------------------------------------+
1 row
in
set
(0.00 sec)
Part4:MySQL Doc
In InnoDB
, all user activity occurs inside a transaction. If autocommit
mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit
enabled, 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 到底有没有效果~ 由于笔者的水平有限,编写时间也很仓促,文中难免会出现一些错误或者不准确的地方,不妥之处恳请读者批评指正。
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-11/148302.htm