数据库内核月报 - 2014 / 12

MySQL · 踩过的坑 · 5.6 GTID 和存储引擎那会事

混用引擎的问题

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

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

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

mysql> SELECT * FROM t1_i;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM t1_m;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+
2 rows in set (0.00 sec)

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

BEGIN;
INSERT INTO t1_m VALUES (3, "test");
INSERT INTO t1_i VALUES (3, "test");
ROLLBACK;
执行回滚后,我们会看到 MySQL 返回信息中有 warnings;
Query OK, 0 rows affected, 1 warning

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

SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
我们来看下现在2张表中的数据,可以看到 t1_m 的插入确实没有回滚,这与事务逻辑的预期是不一样的。

mysql> SELECT * FROM t1_m;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
|    3 | test |
+------+------+

mysql> SELECT * FROM t1_I;
+------+------+
| id   | name |
+------+------+
|    1 | test |
|    2 | test |
+------+------+

当我们在非事务引擎上执行事务相关的操作的时候,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表,会发现是空的。

mysql> SELECT * FROM mysql.user;
Empty set (0.00 sec)

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

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