mysql gtid坑_MySQL内核月报 2014.12-MySQL·　踩过的坑·5.6 GTID 和存储引擎那会事-阿里云开发者社区...

最新推荐文章于 2023-05-24 14:57:39 发布

愁容骑士小新

最新推荐文章于 2023-05-24 14:57:39 发布

阅读量123

点赞数

文章标签： mysql gtid坑

本文链接：https://blog.csdn.net/weixin_28718793/article/details/113428690

版权

混用引擎的问题

在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，然后再升级。