最担心的事情发生了:一个数据丢失惨案

前言

最近,有开发同事联系我反馈一个问题,说开发环境出现了数据丢失的情况,想让DBA帮忙排查一下是不是数据库的问题。我心想大概率是程序bug,不太可能是数据库的问题。

不过还是要排查一下才会心安,毕竟对于一个DBA而言,数据丢失无疑是最令人紧张的一件事情。

问题排查

开始进行排查之前,我先确认了如下问题:

(1)DB实例状态是不是正常的?               
结果:实例状态正常。

(2)开发环境是否还存在?相关表是否存在?            
结果:存在。

(3)应用用户的权限是否正常?               
结果:应用用户拥有所有权限。

(4)业务访问是报什么错?              
 结果:业务侧是访问某些页面报错。

(5)出现问题前系统是否有做什么变更?               
结果:当晚DDL变更有涉及到该业务表的操作,变更内容为修改字段长度,类似alter table xxx modify column xxx char(x)。

(6)该业务表是否有主键?应用日志是否清晰?            
结果:该表有主键,日志未很清晰打印出报错信息。

问题到这里开始有了些许眉目,接下去开始排查sql_mode配置、查询相应的完整行记录给开发确认,最终确认是DDL变更导致字段被截断,最后通过备份进行恢复,问题最终得到解决。

案例复现

为什么修改字段长度对导致数据被截断?MySQL难道不会不会做数据校验吗?相信很多朋友都会有此疑问,让我们把问题复现出来看看。

(1)场景1

最担心的事情发生了:一个数据丢失惨案插图

(2)场景2

最担心的事情发生了:一个数据丢失惨案插图1

我们期望出现的是场景1,直接报错“数据被截断”。

场景2是执行成功,但导致“数据部分丢失”。

MySQL是没有进行数据校验吗?其实MySQL都有对数据进行校验的,只是在场景2中,因为sql_mode配置有问题,没有设置STRICT_TRANS_TABLES,导致MySQL没有阻止该操作执行,从而导致“数据丢失”惨案。

sql_mode详解

sql model 常用来解决下面几类问题

(1) 通过设置sql mode, 可以完成不同严格程度的数据校验,有效地保障数据准备性。

(2) 通过设置sql model 为宽松模式,来保证大多数sql符合标准的sql语法,这样应用在不同数据库之间进行迁移时,则不需要对业务sql 进行较大的修改。

(3) 在不同数据库之间进行数据迁移之前,通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。既然sql_mode是一个非常关键的配置,接下来就带来该配置项的详细解析。

sql_mode最重要的选项:

(1)ANSI:该选项决定SQL语法支持,设置为ANSI,会更加遵守标准SQL语法。

(2)STRICT_TRANS_TABLES:该选项决定数据校验;对于事务性存储引擎,当出现非法值时,该事务会失败并回滚;对于非事务性存储引擎,如果非法值出现在第一行,那么该事务会失败,如果非法值出现在中间,那么会调整非法值,并抛出告警。

(3)TRADITIONAL:该选项决定与传统关系型数据库表现一致;对于一些非正常操作,直接报错失败而不是告警提示。

sql_mode还包括以下选项:

(4)ALLOW_INVALID_DATES:该选项决定不进行严格的日期校验;它只校验月份范围是否为1-12、日期范围是否为1-31,不校验具体日期是否有效,比如2020-04-31这个非法日期是允许的。

(5)ANSI_QUOTES:该选项决定引用字符;它允许双引号”作为引用字符,和反引号`一样。

(6)ERROR_FOR_DIVISION_BY_ZERO:该选项决定被零整除的返回值;如果不启用,那么被零整除的返回值为null且不告警;如果启用但在非严格模式下,那么被零整除的返回值为null且产生告警;如果启用且在严格模式下,那么被零整除会直接报错。

(7)HIGH_NOT_PRECEDENCE:该选项决定not操作的优先级;启用后,NOT a BETWEEN b AND c被解析为NOT (a BETWEEN b AND c);在一些旧版本中,NOT a BETWEEN b AND c被解析为(NOT a) BETWEEN b AND c。

(8)IGNORE_SPACE:该选项决定忽略函数名和括号之间的空格;启用后,比如count (*)也不会报错。

(9)NO_AUTO_CREATE_USER:该选项决定grant语句不会自动创建用户;已过时,grant语句也不会创建用户。

(10)NO_AUTO_VALUE_ON_ZERO:该选项决定自增列的生成;一般来说,向自增列插入0或null,系统会自动生成下一个自增值插入;启用后,向自增列插入0会保留原值0,插入null才会自动生成下一个自增值插入

(11)NO_BACKSLASH_ESCAPES:该选项决定反斜杠\的作用;启用后,反斜杠\不再作为转义字符,而是用作普通字符。

(12)NO_DIR_IN_CREATE:该选项决定忽略创建表时,所有的INDEX DIRECTORY和DATA DIRECTORY指令;该选项只在从库生效。

(13)NO_ENGINE_SUBSTITUTION:该选项决定创建表时,如果指定一个不存在/不支持的存储引擎,那么会自动转换为默认存储引擎。

(14)NO_FIELD_OPTIONS:已过时。

(15)NO_KEY_OPTIONS:已过时。

(16)NO_TABLE_OPTIONS:已过时。

(17)NO_UNSIGNED_SUBTRACTION:一般情况下,整数之间的减法(其中一个为无符号),结果为无符号,如果结果为负数则报错;启用该选项后,负数则可以正常处理。

(18)NO_ZERO_DATE:该选项决定‘0000-00-00’是否可以插入;如果不启用,那么‘0000-00-00’可以插入且不告警;如果启用但在非严格模式下,那么‘0000-00-00’可以插入但会产生告警;如果启用且在非严格模式下,那么‘0000-00-00’不能插入会直接报错。

(19)NO_ZERO_IN_DATE:该选项决定月份和日期是否可以为00;如果不启用,那么月份和日期可以为00且不告警;如果启用但在非严格模式下,那么月份和日期可以为00但会产生告警;如果启用且在非严格模式下,那么月份和日期不能为00会直接报错。

(20)ONLY_FULL_GROUP_BY:该选项决定select/having/order by后面的非聚合字段,必须出现在group by字句中。

(21)PAD_CHAR_TO_FULL_LENGTH:一般情况下,查询char类型的字段,后面的空洞数据会被裁剪;启用该选项后,查询char类型的字段,后面的空洞数据不会被裁剪。

(22)PIPES_AS_CONCAT:该选项决定将||符号当作字符串的连接操作符,而不是当作OR同义词。

(23)REAL_AS_FLOAT:该选项决定将REAL当作FLOAT同义词,而不是DOUBLE同义词。

(24)STRICT_ALL_TABLES:该选项决定数据校验;对于事务性存储引擎,当出现非法值时,该事务会失败并回滚;对于非事务性存储引擎,如果非法值出现在第一行,那么该事务会失败,如果非法值出现在中间,那么前面操作会成功、后面操作会直接报错,出现事务部分成功部分失败的情况。

总结

这个案例提醒我们,sql_mode是一个非常关键的配置,千万不可随便设置和修改。

关于sql_mode如何设置,个人建议如下:

(1)对于5.5/5.6版本,sql_mode建议参照5.7默认值进行设置;

(2)对于5.7版本,sql_mode保持默认值即可;

(3)对于8.0版本,sql_mode也保持默认值即可。

sql_mode的设置,可以让MySQL非常灵活地运行在各种不同模式下,但与此同时也带来各种各样的风险;在MySQL广泛应用于各类重要系统的情况下,建议是要对sql_mode进行严格审核设置,同时对开发代码进行规范化管理;其实这一点,也可以从官方默认值看出来,随着MySQL版本的迭代,sql_mode的设置也是越来越严格。

更多精彩内容,关注我们▼▼

最担心的事情发生了:一个数据丢失惨案插图2

为您推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注