enforce-gtid-consistency打开导致的update更新失败

最新推荐文章于 2025-06-30 13:59:59 发布

原创最新推荐文章于 2025-06-30 13:59:59 发布 · 1.4w 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

技术专栏收录该内容

4 篇文章

订阅专栏

文章详细介绍了在MySQL中启用enforce-gtid-consistency后，遇到的更新非事务表与事务表报错的问题。通过案例分析，展示了在不同执行顺序下，更新InnoDB和MyISAM表的事务处理情况，并提供了关闭该选项作为解决方案。此外，还讨论了主从库在这种配置下的行为表现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

enforce-gtid-consistency打开导致的update更新失败

切换正常，程序运行正常，几日后，业务反应update失败报错，报错如下：

ERROR 1787 (HY000): 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.

问题分析

MySQL版本：5.6.27-76.0-log Percona Server (GPL)
查看数据库中的参数：
gtid_mode = off
enforce-gtid-consistency = on

官方文档解释：
MySQL官方解释说当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporary table语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行

还原场景

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update jump_area_pre_approval set AreaNo=2, AreaName=‘45’, AreaPointAddr=‘rrtr’, AuditReason=‘454’, AuditStatus=5, AuditTime=null, Auditor=‘45’, CreatTime=null, IsDel=5454, OpType=5454, Operator=‘dfd’, ParentID=23, Percent=56, Reason=‘fgf’, ServiceType=56577, Status=4545454, TotalBandWidth=34434, Type=23 where ID=1; #更新innodb表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update jump_area_service_mapping set areaID=4545 where id=1; #更新myisam表
ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
mysql>

两个建表语句

CREATE TABLE jump_area_pre_approval (
ID int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
OpType int(4) NOT NULL DEFAULT ‘0’ COMMENT ‘操作类型,1:新增,2:修改,3:删除’,
ParentID int(10) unsigned DEFAULT NULL,
Reason varchar(255) DEFAULT NULL COMMENT ‘提交原因’,
AreaNO int(4) NOT NULL DEFAULT ‘0’ COMMENT ‘区域编号,0-512’,
AreaName varchar(50) NOT NULL COMMENT ‘区域名称’,
Percent float NOT NULL DEFAULT ‘0’ COMMENT ‘百分比’,
ProductID int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘产品ID,和AreaID共同确定一条记录product表PK’,
TotalBandWidth int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘总带宽’,
Status int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘区域状态’,
CreatTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘创建时间’,
AuditTime timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘审核时间’,
AuditStatus int(10) NOT NULL DEFAULT ‘0’ COMMENT ‘审核状态,0:待审核,1:审核通过,2:审核不通过’,
AuditReason varchar(255) DEFAULT NULL COMMENT ‘审核结果’,
IsDel int(10) NOT NULL DEFAULT ‘1’ COMMENT ‘删除标示,1:可用,2:删除’,
Operator varchar(45) DEFAULT NULL COMMENT ‘操作人’,
Auditor varchar(45) DEFAULT NULL COMMENT ‘审核人’,
Type int(10) NOT NULL COMMENT ‘区域类型 1:a 2:b’,
AreaPointAddr varchar(255) DEFAULT NULL,
ServiceType int(10) NOT NULL COMMENT ‘区域服务类型 1:自建 2:合作’,
PRIMARY KEY (ID)
) ENGINE=InnoDB AUTO_INCREMENT=5267 DEFAULT CHARSET=utf8

CREATE TABLE jump_area_service_mapping (
ID int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
areaID int(10) unsigned NOT NULL COMMENT ‘区域ID’,
serviceID int(10) unsigned NOT NULL COMMENT ‘服务ID’,
creatTime timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘创建时间’,
PRIMARY KEY (ID)
) ENGINE=MyISAM AUTO_INCREMENT=9298 DEFAULT CHARSET=utf8

解决方案

将enforce-gtid-consistency关闭，重启就好了

延伸扩展

1、当enforce-gtid-consistency=1，gtid_mode = off时主库执行
先执行innodb，再执行myisam，报错

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update jump_area_pre_approval set AreaNo=2, AreaName=‘45’, AreaPointAddr=‘rrtr’, AuditReason=‘454’, AuditStatus=5, AuditTime=null, Auditor=‘45’, CreatTime=null, IsDel=5454, OpType=5454, Operator=‘dfd’, ParentID=23, Percent=56, Reason=‘fgf’, ServiceType=56577, Status=4545454, TotalBandWidth=34434, Type=5676778 where ID=1; #innodb表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update jump_area_service_mapping set areaID=4545 where id=1; #mysiam表
ERROR 1785 (HY000): When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

先执行myisam，再执行innodb，不报错

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update jump_area_service_mapping set areaID=4545 where id=1; #myisam表
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> update jump_area_pre_approval set AreaNo=2, AreaName=‘45’, AreaPointAddr=‘rrtr’, AuditReason=‘454’, AuditStatus=5, AuditTime=null, Auditor=‘45’, CreatTime=null, IsDel=5454, OpType=5454, Operator=‘dfd’, ParentID=23, Percent=56, Reason=‘fgf’, ServiceType=56577, Status=4545454, TotalBandWidth=34434, Type=5676778 where ID=1; #innodb表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

原因：先执行myisam，虽然begin开启了事务，但并没有真正开启事务，继而在对innodb操作的时候，事务里面仅有对innodb的操作，所以不会违反GTID的强一致性。但反过来，先执行innodb，则已经开启了事务，再对myisam操作，表示一个事务里有事务表和非事务表，进而违反了GTID的强一致性，就会报错

2、主库：enforce-gtid-consistency=0，gtid_mode = off，
从库：enforce-gtid-consistency=1，gtid_mode = off

mysql> begin; #主库执行
Query OK, 0 rows affected (0.00 sec)
mysql> update jump_area_pre_approval set AreaNo=2, AreaName=‘45’, AreaPointAddr=‘rrtr’, AuditReason=‘454’, AuditStatus=5, AuditTime=null, Auditor=‘45’, CreatTime=null, IsDel=5454, OpType=5454, Operator=‘dfd’, ParentID=23, Percent=56, Reason=‘fgf’, ServiceType=56577, Status=4545454, TotalBandWidth=1215, Type=23 where ID=1; #innodb表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update jump_area_service_mapping set areaID=1215 where id=1;#myisam表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

现象描述：这种情况，在主库执行，不报错是对的，但传到从库应该报错，奇怪，为什么不报错？
原因：主库先update innodb表，再update myisam表，主库binlog记录的顺序是：先myisam，再innodb。。继而传到从库，就不会报错啦~

更多有关binlog记录顺序，请参考：http://blog.51cto.com/linzhijian/2063741