签到成功

知道了

CNDBA社区CNDBA社区

MySQL CTAS ERROR 1786 (HY000) Statement violates GTID consistency 错误 解决方法

2023-10-08 09:22 696 0 原创 MySQL
作者: dave

1 问题现象

在MySQL 5.7 中执行CTAS 报错:http://www.cndba.cn/dave/article/131451

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.00 sec)

mysql> create table ustc.dave as select * from mysql.user;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

2 分析说明

MySQL5.6之后启用GTID的限制导致。

http://www.cndba.cn/dave/article/131451

mysql> select @@GLOBAL.ENFORCE_GTID_CONSISTENCY;
+-----------------------------------+
| @@GLOBAL.ENFORCE_GTID_CONSISTENCY |
+-----------------------------------+
| ON                                |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select @@gtid_mode;
+-------------+
| @@gtid_mode |
+-------------+
| ON          |
+-------------+
1 row in set (0.00 sec)

GTID的限制如下:

http://www.cndba.cn/dave/article/131451

1.不支持非事务引擎
2.不支持create table … select 语句复制,原因: 会生成两个sql,一个是DDL创建表SQL,一个是insert into 插入数据的sql。由于DDL会导致自动提交,所以这个sql至少需要两个GTID,但是GTID模式下,只能给这个sql生成一个GTID 。
3.不允许一个SQL同时更新一个事务引擎表和非事务引擎表
4.在一个复制组中,必须要求统一开启GTID或者是关闭GTID
5.开启GTID需要重启(5.7除外)
6.开启GTID后,就不再使用原来的传统复制方式
7.对于create temporary table 和 drop temporary table语句不支持
8.不支持sql_slave_skip_counterhttp://www.cndba.cn/dave/article/131451http://www.cndba.cn/dave/article/131451

3 解决方法

3.1 方法一:关闭GTID

在线关闭 : http://www.cndba.cn/dave/article/131451

set @@global.gtid_mode = on_permissive;
set @@global.gtid_mode = off_permissive;
set @@global.gtid_mode = off;
set @@global.enforce_gtid_consistency = off;

在线启用:http://www.cndba.cn/dave/article/131451

set @@global.gtid_mode = off_permissive ;
set @@global.gtid_mode = on_permissive;
set @@global.enforce_gtid_consistency = on;
set @@global.gtid_mode = on;

修改配置文件:

http://www.cndba.cn/dave/article/131451

enforce_gtid_consistency = off;
gtid_mode = off;

3.2 方法二:拆解操作

将CTAS 拆解为两部分:http://www.cndba.cn/dave/article/131451

1.create table 表A like 表B ;
2.insert into 表A select *from 表B ;http://www.cndba.cn/dave/article/131451

mysql> create table cndba.ustc like mysql.user;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into cndba.ustc select * from mysql.user;
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> select count(1) from cndba.usts;
+----------+
| count(1) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2241
    原创
  • 3
    翻译
  • 550
    转载
  • 187
    评论
  • 访问:6767951次
  • 积分:4258
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ