ERROR 1786 (HY000)
【環境描述】
msyql5.6.14
【報錯資訊】
執行create table ... select的時候遇到報錯：
db1 [test] [23:01:58]> 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語句或事務都不允許執行。
db1 [test] [23:28:28]> show variableslike 'ENFORCE_GTID_CONSISTENCY';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON |
+--------------------------+-------+
【解決方法】
由于enforce_gtid_consistency參數是隻讀的，是以必須重新開機MySQL服務才能是配置生效。
嘗試線上動态修改時的報錯：
db1 [test] [23:37:56]> set globalenforce_gtid_consistency=true;
ERROR 1238 (HY000): Variable'enforce_gtid_consistency' is a read only variable
下面是其他網友的補充
一般mysql5.7以前版本是支援create table XXX as select * from XXX; 這種建立表的文法，但是MySQL5.7.x版本裡面gtid是開啟的，會報錯
ERROR 1786 (HY000):Statement violates GTID consistency: CREATE TABLE ... SELECT.
CREATE TABLE ... SELECT statements. CREATE TABLE ... SELECT is not safe for statement-based replication. When using row-based replication, this statement is actually logged as two separate events—one for the creation of the table, and another for the insertion of rows from the source table into the new table just created. When this statement is executed within a transaction, it is possible in some cases for these two events to receive the same transaction identifier, which means that the transaction containing the inserts is skipped by the slave. Therefore, CREATE TABLE ... SELECT is not supported when using GTID-based replication.
解決辦法關閉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)
到此這篇關于mysql錯誤處理之ERROR 1786 (HY000)的文章就介紹到這了,更多相關mysql錯誤處理之ERROR 1786 (HY000)内容請搜尋腳本之家以前的文章或繼續浏覽下面的相關文章希望大家以後多多支援腳本之家！