mysql错误处理之ERROR 1786 (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)
到此这篇关于mysql错误处理之ERROR1786(HY000)的文章就介绍到这了,更多相关mysql错误处理之ERROR1786(HY000)内容请搜索毛票票以前的文章或继续浏览下面的相关文章希望大家以后多多支持毛票票！