MySQL错误ERROR 1786 (HY000)解决

【环境描述】

msyql5.6.14

【报错信息】

mysql> create tablelgmnr_bak select * from lgmnr;

ERROR 1786 (HY000): CREATE TABLE ... SELECTis forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1

【报错原因】

ERROR1786是由于开启了enforce_gtid_consistency=true功能导致的，MySQL官方解释说当启用enforce_gtid_consistency功能的时候，MySQL只允许能够保障事务安全，并且能够被日志记录的SQL语句被执行，像create table ... select 和 create temporarytable语句，以及同时更新事务表和非事务表的SQL语句或事务都不允许执行。

mysql> show variableslike 'ENFORCE_GTID_CONSISTENCY'; 

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

| Variable_name | Value |

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

| enforce_gtid_consistency | ON |

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

【解决方法】

关闭GTID模式

my.cnf里面修改参数为：

gtid_mode = OFF
enforce_gtid_consistency = OFF

重启MySQL，再次创建成功：

mysql> show variables like '%gtid_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| gtid_mode | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like '%enforce_gtid_consistency%';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | OFF |
+--------------------------+-------+
1 row in set (0.01 sec)
mysql> create table t1 as select * from BS_CONT;
Query OK, 0 rows affected (0.12 sec)