MySQL内核月报 2014.12-MySQL· 踩过的坑·5.6 GTID 和存储引擎那会事

本文涉及的产品
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:

混用引擎的问题

在MySQL中,存储引擎是通过插件方式使用的,事务是由存储引擎自己实现,MySQL服务层是不管理事务的,所以在同一个事务中混用不同的存储引擎是不可靠的。 如果混用事务引擎和非事务引擎的话,事务如果正常提交的话,5.5不会有问题,但是5.6版本如果开了 GTID 的话就会报错,因为GTID模式下不允许事务中同时更新事务引擎和非事务引擎(Restrictions on Replication with GTIDs); 如果事务回滚的话,不管是 5.5 还是 5.6 都会有问题的,因为对非事务引擎表的操作是无法回滚的,这样就会造成数据不一致,因为只有部分操作成功,并且结果不可预知,事务的原子性和一致性被破坏。

我们下面举例子来说明,t1_i 是事务引擎表(InnoDB),ti_m 是非事务引擎(MyISAM)。

在事务执行前2张表里的数据如下:

 

事务如下,对2张表分别插一条数据,然后 rollback 模拟出错回滚,

 

执行回滚后,我们会看到 MySQL 返回信息中有 warnings;
Query OK, 0 rows affected, 1 warning

查看 warning,可以清楚地看到提示,非事务引擎无法回滚:

 

我们来看下现在2张表中的数据,可以看到 t1_m 的插入确实没有回滚,这与事务逻辑的预期是不一样的。

 

当我们在非事务引擎上执行事务相关的操作的时候,MySQL 一般是不会报错的,比如在非事务引擎的操作前后用 BEGIN 和 COMMIT, 这是完全可以的,并且让我们误以为操作是有事务性的,我们在使用的时候要注意这一点。

总的来说,要遵循这样的一条原则,不要在事务中混用不同的存储引擎。

5.5 到 5.6 升级问题

5.5 升级到 5.6 的时候,我们会先建立一个5.6版本的实例,然后把数据从5.5迁移到5.6,最后把连接切到 5.6 完成升级。有时候升级会失败,这是因为5.6 GTID 的一些限制导致的。

其中有一条是关于临时表的限制,官方描述如下:

Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not supported inside transactions when using GTIDs (that is, when the server was started with the --enforce-gtid-consistency option). It is possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.

限制表明不能在事务中创建临时表,可以在事务外创建,但要求 autocommit = 1。

我们新建5.6的实例的时候,会重用 5.5 的配置,autocommit 就是其中之一,并且这个是允许用户配置的,如果用户在5.5实例上把这个值改为0,然后升级到5.6,就会出错。我们知道,创建新实例的时候,会先通过 mysql_install_db 脚本初始化数据库,这包括系统表的创建、基本数据的添加等 ,其中会用到 mysql_system_tables_data.sql 这个sql脚本,里面有:

CREATE TEMPORARY TABLE tmp_db LIKE db; 
CREATE TEMPORARY TABLE tmp_user LIKE user; 
CREATE TEMPORARY TABLE tmp_proxies_priv LIKE proxies_priv; 

这样的语句,脚本执行失败,所以mysql.db 、mysql.user 和 mysql.proxies_priv表里的初始数据就没有添加进去。

error log 中会看到这样的信息

2014-12-08 20:48:15 9264 [Warning] Bootstrap mode disables GTIDs. Bootstrap mode should only be used by mysql_install_db which initializes the MySQL data directory and creates system tables. ERROR: 1787 When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1. 2014-12-08 20:48:16 9264 [ERROR] Aborting

我们这时如果正常启动mysqld的话,会发现默认root用户是登录不进去的,因为user表是空的,根本没有root用户,而匿名用户 @localhost 又什么也做不了。

我们用 --skip-grant-tables 启动数据库后,匿名用户登录进去,查看user表,会发现是空的。

 

因为新建的数据库不可用,所以最终导致迁移失败。

这个问题的根本原因是5.5的配置文件中 autocommit = 0 导致的,所以好的解决方法是我们在升级5.6前,把这个值设置为1,然后再升级。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6月前
|
存储 缓存 关系型数据库
MySQL 存储引擎
MySQL 存储引擎
65 6
|
3月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID:如何让数据库备份恢复变得轻松简单?
【8月更文挑战第22天】MySQL的binlog日志记录数据变更,用于恢复、复制和点恢复;GTID为每笔事务分配唯一ID,简化复制和恢复流程。开启binlog和GTID后,可通过`mysqldump`进行逻辑备份,包含binlog位置信息,或用`xtrabackup`做物理备份。恢复时,使用`mysql`命令执行备份文件,或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
286 2
|
6天前
|
存储 SQL 关系型数据库
MySQL存储引擎
本文介绍了数据库优化的多个方面,包括选择合适的存储引擎、字段定义原则、避免使用外键和触发器、大文件存储策略、表拆分及字段冗余处理等。强调了从业务层面进行优化的重要性,如通过活动设计减少外部接口调用,以及在高并发场景下的流量控制与预处理措施。文章还提供了具体的SQL优化技巧和表结构优化建议,旨在提高数据库性能和可维护性。
MySQL存储引擎
|
20天前
|
存储 SQL 缓存
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
MySQL存储引擎如何完成一条更新语句的执行!
|
2月前
|
存储 缓存 关系型数据库
MySQL高级篇——存储引擎和索引
MyISAM:不支持外键和事务,表锁不适合高并发,只缓存索引,内存要求低,查询快MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务、行级锁、外键,有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构;表名.MYD 存储数据 (MYData);
MySQL高级篇——存储引擎和索引
|
5月前
|
Prometheus 监控 关系型数据库
数据库同步革命:MySQL GTID模式下主从配置的全面解析
数据库同步革命:MySQL GTID模式下主从配置的全面解析
551 0
|
3月前
|
存储 关系型数据库 MySQL
MySQL 中的事务存储引擎深入解析
【8月更文挑战第31天】
50 0
|
5月前
|
存储 关系型数据库 MySQL
|
4月前
|
存储 关系型数据库 MySQL
MySQL InnoDB存储引擎的优点有哪些?
上述提到的特性和优势使得InnoDB引擎非常适合那些要求高可靠性、高性能和事务支持的场景。在使用MySQL进行数据管理时,InnoDB通常是优先考虑的存储引擎选项。
152 0
|
5月前
|
存储 关系型数据库 MySQL
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)
MySQL数据库进阶第一篇(存储引擎与Linux系统上安装MySQL数据库)

相关产品

  • 云数据库 RDS MySQL 版