mysql错误处理之ERROR1786(HY000)

ERROR1786(HY000)

【环境描述】

msyql5.6.14

【报错信息】

执行createtable...select的时候遇到报错：

db1[test][23:01:58]>createtablelgmnr_bakselect*fromlgmnr;
ERROR1786(HY000):CREATETABLE...SELECTisforbiddenwhen@@GLOBAL.ENFORCE_GTID_CONSISTENCY=1

【报错原因】

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

db1[test][23:28:28]>showvariableslike"ENFORCE_GTID_CONSISTENCY";

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

|Variable_name|Value|

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

|enforce_gtid_consistency|ON|

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

【解决方法】

由于enforce_gtid_consistency参数是只读的，所以必须重启MySQL服务才能是配置生效。

尝试在线动态修改时的报错：

db1[test][23:37:56]>setglobalenforce_gtid_consistency=true;
ERROR1238(HY000):Variable"enforce_gtid_consistency"isareadonlyvariable

下面是其他网友的补充

一般mysql5.7以前版本是支持createtableXXXasselect*fromXXX;这种创建表的语法，但是MySQL5.7.x版本里面gtid是开启的，会报错

ERROR1786(HY000):StatementviolatesGTIDconsistency:CREATETABLE...SELECT.

官方说明：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html

CREATETABLE...SELECTstatements.CREATETABLE...SELECTisnotsafeforstatement-basedreplication.Whenusingrow-basedreplication,thisstatementisactuallyloggedastwoseparateevents—oneforthecreationofthetable,andanotherfortheinsertionofrowsfromthesourcetableintothenewtablejustcreated.Whenthisstatementisexecutedwithinatransaction,itispossibleinsomecasesforthesetwoeventstoreceivethesametransactionidentifier,whichmeansthatthetransactioncontainingtheinsertsisskippedbytheslave.Therefore,CREATETABLE...SELECTisnotsupportedwhenusingGTID-basedreplication.

解决办法关闭GTID模式：

my.cnf里面修改参数为：

gtid_mode=OFF
enforce_gtid_consistency=OFF

重启MySQL，再次创建成功：

mysql>showvariableslike"%gtid_mode%";
+---------------+-------+
|Variable_name|Value|
+---------------+-------+
|gtid_mode|OFF|
+---------------+-------+
1rowinset(0.01sec)

mysql>showvariableslike"%enforce_gtid_consistency%";
+--------------------------+-------+
|Variable_name|Value|
+--------------------------+-------+
|enforce_gtid_consistency|OFF|
+--------------------------+-------+
1rowinset(0.01sec)

mysql>createtablet1asselect*fromBS_CONT;
QueryOK,0rowsaffected(0.12sec)