首页 > 其他分享 >一个数据丢失惨案

一个数据丢失惨案

时间:2023-07-31 09:34:01浏览次数:36  
标签:选项 00 启用 NO 惨案 mode sql 数据 丢失

前言

最近,有开发同事联系我反馈一个问题,说开发环境出现了数据丢失的情况,想让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,直接报错“数据被截断”。

场景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的设置也是越来越严格。

标签:选项,00,启用,NO,惨案,mode,sql,数据,丢失
From: https://www.cnblogs.com/shujuyr/p/17592598.html

相关文章

  • redis常见的5中数据类型以及相关命令
    redisredis简介redis是采用ASNIC语言编写的采用的是C/S架构是非关系型数据库以键值对的形式存储在内存中redis在windows中的启动命令redis-serverredis.windows.confredis-cli切换到客户端一、redis的数据类型1.String(字符串)最基本的数据结构,可以......
  • 职业规划~高级大数据工程师篇
    新开分类,增加职业规划随想,用于记录自己对于以后的职业畅想。畅想自己在2019-2023年这4年期间,自己从基础的平台开发运营,到各种开源组件的学习应用,再到一个CRUD的大数据工程师。自己一路走来,更多是像一名技师,孰能生巧,唯手熟尔。如今开一博客,记录日常解决问题,新技术探索学习。也......
  • 基于面向对象的数据封装
    基于面向对象的数据封装目录基于面向对象的数据封装1基于面向对象的数据封装优点2示例1基于面向对象的数据封装优点将属性和方法放到一起做为一个整体,然后通过实例化对象来处理;隐藏内部实现细节,只需要和对象及其属性和方法交互就可以了;对类的属性和方法增加访问权限控制......
  • Map和Object:JS如何根据需求选择正确的键值对数据结构
    Map和Object都是JavaScript中常用的数据结构,它们都可以用来存储键值对(key-valuepairs)。但是,它们之间也有一些重要的区别,了解这些区别可以帮助我们选择更合适的数据结构来满足我们的需求。公众号:Code程序人生,个人网站:https://creatorblog.cnObject的特点Object是JavaScript中最基本......
  • 基于AI的数据增广:生成数据作为训练样本
    BenchmarkingandAnalyzingGenerativeDataforVisualRecognition大型预训练生成模型的进展,扩展了它们作为有效数据生成器在视觉识别中的潜力。本研究深入探讨了生成图像的影响,主要比较了使用外部数据(即生成vs.网络检索vs.原始)的范式。主要贡献是:1)GenBench构建:设计了GenBen......
  • 并发给数据库带来的问题及解决方案
    假设没有事务的隔离性,当两个事务并发地去操作数据库的表或者行的时候,那么会产生什么问题?1并发给数据库带来的问题1、读脏数据A事务修改了一个数据(假设A从1改为2),B事务读取A事务修改后的数据(B读取到了2),但由于某种原因A事务撤销了事务(这个数据回滚到1),此时导致B事务读......
  • 如何降低TCP在局域网环境下的数据传输延迟
    以Ping为例。本案例是一个测试题目,只有现象展示,不含解决方案。ROS_Kinetic_26使用rosserial_windows实现windows与ROSmaster发送与接收消息_windows接收ros1消息什么是ping?AI:ping是互联网控制消息协议(InternetControlMessageProtocol,ICMP)的一个应用。ping命令是一种简单的网......
  • 使用Python脚本实现ElasticSearch的在线数据迁移
    该脚本的功能,类似于elasticsearch-dump,二者都是基于scroll来实现的(包括reindex底层也是scroll)。依赖包#我这里演示的ES是7.x的,如果下面的脚本运行报错,请考虑调整这里的python的elasticsearch包版本pipinstallelasticsearch==7.13.1配置文件vimconfigs.py#-*-coding:utf-8......
  • 活动回顾|火山引擎 DataLeap 分享:DataOps、数据治理、指标体系最佳实践(文中领取 PPT)
    更多技术交流、求职机会,欢迎关注字节跳动数据平台微信公众号,回复【1】进入官方交流群 在7月21日至22日举行的 ArchSummit全球架构师峰会(深圳站)及 DataFunCon.数据智能创新与实践大会(北京站)上,来自火山引擎DataLeap的三位专家分别就 DataOps、数据治理和指标体系等......
  • 12-MySQL数据库的触发器
    12-MySQL数据库的触发器课程目标了解触发器简介。掌握MySQL触发器的创建和使用以及删除。12.1触发器简介触发器是一个被指定关联到一个表的数据库对象,当对一个表的特定事件(例如增、删、改、查表中记录)出现时,它将会被激活。触发器具有MySQL语句在需要时才被执行的特点,即某条(或某......