MySQL · 捉虫动态·DROP DATABASE外键约束的GTID BUG

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 背景 MySQL的DDL没有被设计成事务操作,因此DDL操作是无法回滚的(像PgSQL把DDL也设计成事务操作,DDL就可以在执行成功后被回滚操作取消)。这就会导致如果某个DDL语句内部被拆分为多个原子的DDL调用,那么这个DDL语句就不具备中途执行失败后回滚整个DDL语句的能力,也就是说,即使语

背景

MySQL的DDL没有被设计成事务操作,因此DDL操作是无法回滚的(像PgSQL把DDL也设计成事务操作,DDL就可以在执行成功后被回滚操作取消)。这就会导致如果某个DDL语句内部被拆分为多个原子的DDL调用,那么这个DDL语句就不具备中途执行失败后回滚整个DDL语句的能力,也就是说,即使语句逻辑内的某个原子DDL调用失败了,也无法回滚已经完成的那些原子DDL调用。


问题描述

DROP DATABASE 就是一个例子,对于MySQL而言,DROP DATABASE 并非是一个原子DDL操作,因为它是一个个删除DB下的每张表,而 DROP TABLE 操作本身是会做预检查的,无法删除就会取消删表操作返回失败,所以 DROP TABLE 才能认为是原子的DDL调用。 这就会引起一个问题,如果一个DB中的某张表DROP失败了,实际上 DROP DATABASE 作为一个整体是执行失败的,但是DB中已经有一些表被删除了,因此Binlog中会记录成多个 DROP TABLE 操作,而不是一个 DROP DATABASE 语句。 如果被删除的表的表名都不长,还是会记录成一个删除多张表的 DROP TABLE 语句(DROP TABLE tbl1, tbl2, ...),但是如果表名总长度太长,MySQL会拆分为多个 DROP TABLE 语句来记录。 没有GTID的时候这似乎也不是什么大问题,但是引入GTID之后就有一个问题:每个语句只分配一个GTID。如果一个 DROP DATABASE 语句被拆分为多个 DROP TABLE 语句,Binlog中就会出现多个 DROP TABLE 事件共用一个GTID的情况!

举个例子:

 

这里因为 db2.t3 表引用了 db1.t1 的字段作为外键约束,所以当 db1 做 DROP DATABASE 删除到 t1 表时就报错了,但此时很多表已经被删除了。我们看Binlog中记录的内容:

 

3个 DROP TABLE 语句都是同一个GTID:340d95b8-a699-11e4-868d-a0d3c1f20ae4:61

这就导致备库复制报错:

 


解决方案

怎么解决这个问题呢?

1. 让MySQL支持DDL事务

2. 对DROP DATABASE操作进行预检查


第一种方案对MySQL改动太大了,完全不现实。因此我们采用了第二种方案,也间接实现了 DROP DATABASE 这个操作的原子性。 DROP DATABASE 之所以出现上面的状况,就是因为没有先检查表是否可以删除,而是走一步看一步,一个个删的时候才看能不能删除。我们对MySQL做了修正,对于DB中的每张表,在 DROP DATABASE 执行之前,都先预检查所有可能导致删除表失败的条件,如果一旦发现某张表会无法删除,就放弃整个 DROP DATABASE 操作,提示用户删除错误,让用户先自行解决问题后,再重新执行 DROP DATABASE。

例如上面例子中的情况,本来 DROP DATABASE 执行到有外键约束的表时会报错:

 

但此时其他表已经删除了,而我们修正以后,同样的操作会报一个Error和一个Warning,并且没有真的删任何表:

 

这里提示了用户有表存在问题无法删除,让用户先处理掉之后,再来执行 DROP DATABASE。此时库下面所有的表都还在,一定要预检查通过才会真的删除。


相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
db匠
+关注
目录
打赏
0
0
0
0
9495
分享
相关文章
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
环比、环比增长率、同比、同比增长率 ,占比,Mysql 8.0 实例(最简单的方法之一)(sample database classicmodels _No.2 )
184 1
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
75 0
全表数据核对 ,行数据核对,列数据核对,Mysql 8.0 实例(sample database classicmodels _No.3 )
2024Mysql And Redis基础与进阶操作系列(4-2)作者——LJS[含MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法]
24MySQL非空、唯一性、PRIMARY KEY、自增列/自增约束举例说明等详解步骤及常见报错问题对应的解决方法(4-2) 学不会你来砍我!!!
【MySQL基础篇】MySQL约束语法
文章介绍了MySQL中表的约束概念,包括非空、唯一、主键、默认和外键约束,以及如何在创建和修改表时指定这些约束。外键约束用于保持数据的一致性和完整性,文章通过示例展示了添加、删除外键的语法,并讨论了不同的删除/更新行为,如CASCADE和SETNULL。
【MySQL基础篇】MySQL约束语法
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
669 2
MySQL(条件约束)
为了校验数据,让数据的正确性能够得到保证,约束,能够引进更多的检查操作,但是也会增加系统的成本开销
MySQL函数与约束
MySQL 提供了丰富的函数和强大的约束机制,用于数据处理和完整性维护。通过掌握这些工具,可以有效地管理和分析数据库中的数据,确保数据的一致性和准确性。无论是在日常数据查询中使用内置函数,还是在数据库设计中应用各种约束,都是确保数据库系统稳定、高效运行的关键。希望本文对您理解和应用 MySQL 函数与约束有所帮助。
43 1
mysql 8.0 递归(例子分享)(sample database classicmodels _No.4)
本文介绍了如何在MySQL8.0中使用递归查询处理部门表和员工表的树形结构数据,包括查看文档、准备数据、递归处理以及提取层级信息。作者通过示例展示了WITHRECURSIVE语句的应用及其在数仓中的结构表示。
57 2
mysql8.0 正值表达式Regular expressions (sample database classicmodels _No.5)
本文介绍了MySQL8.0中的正值表达式及其相关函数,通过实例展示了如何使用正则表达式进行字符串匹配,并提出了关于执行效率的问题。
107 1
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
ubuntu使用aliyun源+mysql删除有外键约束的数据+查看特定目录的大小
95 4

相关产品

  • 云数据库 RDS MySQL 版