ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

创建表时报错，如下：

mysql> create table t_idb_big as select * from information_schema.columns;

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

mysql>

错误原因：

这是因为在5.6及以上的版本内，开启了 

enforce_gtid_consistency=true

 功能导致的，MySQL官方解释说当启用 

enforce_gtid_consistency

 功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table … select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

解决方法：

方法一：

修改 ：SET @@GLOBAL.ENFORCE_GTID_CONSISTENCY = off;

配置文件中 ：ENFORCE_GTID_CONSISTENCY = off;

方法二（测试成功）：

将 create table xxx as select 的方式拆分成两部分。

create table xxxx like data_mgr;

insert into xxxx select *from data_mgr;

1.当设置ENFORCE_GTID_CONSISTENCY = off时，错误消息说GTID_MODE = ON时需要ENFORCE_GTID_CONSISTENCY = ON，也就是需要设置GTID_MODE = OFF才能设置ENFORCE_GTID_CONSISTENCY = off

mysql> SET GLOBAL ENFORCE_GTID_CONSISTENCY = off;

ERROR 1779 (HY000): GTID_MODE = ON requires ENFORCE_GTID_CONSISTENCY = ON.

mysql> 

2.设置GTID_MODE = OFF

mysql> set global GTID_MODE = off;

ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

mysql>

上面提示如果当前值为ON，要设置为OFF，则先设置为GTID_MODE=ON_PERMISSIVE，再设置GTID_MODE=OFF_PERMISSIVE，再设置GTID_MODE = off，如果将OFF设置为ON，则反过来设置即可。

继续设置：

mysql> set @@GLOBAL.GTID_MODE=ON_PERMISSIVE;

Query OK, 0 rows affected (0.01 sec)

mysql> set @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

ERROR 1766 (HY000): The system variable gtid_mode cannot be set when there is an ongoing transaction.

上面报错，当有正在进行的事务时，不能设置，所以就COMMIT一下：

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> set @@GLOBAL.GTID_MODE=OFF_PERMISSIVE;

Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> set @@GLOBAL.GTID_MODE=OFF;

Query OK, 0 rows affected (0.01 sec)

mysql> 

mysql> 

mysql> show variables like 'GTID_MODE';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| gtid_mode     | OFF   |

+---------------+-------+

1 row in set (0.00 sec)

mysql> 

3.然后再设置SET GLOBAL ENFORCE_GTID_CONSISTENCY = off：

mysql> SET GLOBAL ENFORCE_GTID_CONSISTENCY = off;

Query OK, 0 rows affected (0.00 sec)

mysql>

2019.11.07