案例| +1s导致的故障

用户1278550

发布于 2020-08-21 15:07:01

5210

发布于 2020-08-21 15:07:01

文章被收录于专栏：idbaidba

该图片由Simon Steinberger在Pixabay上发布

前言

本周有一个业务开发同学要刷数据将 datetime 类型的字段增加1s，但是部分字段的内容变成了 "0000-00-00 00:00:00" 导致相关业务查询数据不一致。具体开发怎么操作的呢？又触发了什么开关导致数据不一致的呢？

问题场景

数据库版本Percona 5.7.22

环境配置

sql_mode: NO_ENGINE_SUBSTITUTION

表结构

 CREATE TABLE `dt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  `b` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

复现

更新字段的sql 其实很简单update dt set a=a+1 ,b=b+1 where 条件

test [RW] 11:11:38 >select * from dt;
+-------+---------------------+---------------------+
| id    | a                   | b                   |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:50 | 2020-08-20 12:08:59 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)

test [RW] 11:11:46 >update dt set  a=a+1 ,b=b+1 ;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

test [RW] 11:12:09 >show  warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

 test [RW] 11:12:17 >select * from dt;
+-------+---------------------+---------------------+
| id    | a                   | b                   |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:51 | 0000-00-00 00:00:00 |
+-------+---------------------+---------------------+
1 row in set (0.00 sec)

我们可以看到 a字段 "2020-08-20 12:08:50"变成"2020-08-20 12:08:51"，时间戳增加1s，符合预期。但是b字段的值从 2020-08-20 12:08:59 变成 0000-00-00 00:00:00，明显不符合预期。这也是导致后面查询某些时间段的数据，导致正常的数据缺失的。

分析

在MySQL 中对于datetime 和 time类型而言秒级数据的有效值是 0-59，在这个范围内加减都可以，但是结果值超过59秒就会溢出，提示 Data truncated for column 'b'。

在严格模式下,针对 59秒直接+1s的时候，直接报错，而不是像 NO_ENGINE_SUBSTITUTION 时给一个warning值。

test [RW] 11:36:15 >show variables like '%sql_mode%';
+---------------+---------------------+
| Variable_name | Value               |
+---------------+---------------------+
| sql_mode      | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)
test [RW] 11:36:18 >insert into dt(a,b) values('2020-08-20 12:08:50','2020-08-20 12:08:59');
Query OK, 1 row affected (0.00 sec)

test [RW] 11:36:36 >select * from  dt;
+-------+---------------------+---------------------+
| id    | a                   | b                   |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:52 | 0000-00-00 00:00:00 |
| 10032 | 2020-08-20 12:08:50 | 2020-08-20 12:08:59 |
+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

test [RW] 11:36:44 >update  dt set  a=a+1 ,b=b+1  where id=10032;
ERROR 1292 (22007): Incorrect datetime value: '20200820120860' for column 'b' at row 1

正确的方式是什么呢？合理的利用 data_add() 函数。

test [RW] 11:40:47 >update dt set a=date_add(a,interval 1 second),b=date_add(b,interval 1 second) where id=10032;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 test [RW] 11:41:10 >select * from dt;
+-------+---------------------+---------------------+
| id    | a                   | b                   |
+-------+---------------------+---------------------+
| 10031 | 2020-08-20 12:08:52 | 0000-00-00 00:00:00 |
| 10032 | 2020-08-20 12:08:51 | 2020-08-20 12:09:00 |
+-------+---------------------+---------------------+
2 rows in set (0.00 sec)