ERROR 1786 (HY000)

【環(huán)境描述】

msyql5.6.14

【報(bào)錯(cuò)信息】

執(zhí)行create table ... select的時(shí)候遇到報(bào)錯(cuò)：

db1 [test] [23:01:58]> create tablelgmnr_bak select * from lgmnr;
ERROR 1786 (HY000): CREATE TABLE ... SELECTis forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1

【報(bào)錯(cuò)原因】

ERROR1786是由于開(kāi)啟了enforce_gtid_consistency=true功能導(dǎo)致的，MySQL官方解釋說(shuō)當(dāng)啟用enforce_gtid_consistency功能的時(shí)候，MySQL只允許能夠保障事務(wù)安全，并且能夠被日志記錄的SQL語(yǔ)句被執(zhí)行，像create table ... select 和 create temporarytable語(yǔ)句，以及同時(shí)更新事務(wù)表和非事務(wù)表的SQL語(yǔ)句或事務(wù)都不允許執(zhí)行。

db1 [test] [23:28:28]> show variableslike 'ENFORCE_GTID_CONSISTENCY';

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

| Variable_name  | Value |

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

| enforce_gtid_consistency | ON |

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

【解決方法】

由于enforce_gtid_consistency參數(shù)是只讀的，所以必須重啟MySQL服務(wù)才能是配置生效。

嘗試在線動(dòng)態(tài)修改時(shí)的報(bào)錯(cuò)：

db1 [test] [23:37:56]> set globalenforce_gtid_consistency=true;
ERROR 1238 (HY000): Variable'enforce_gtid_consistency' is a read only variable

下面是其他網(wǎng)友的補(bǔ)充

一般mysql5.7以前版本是支持create table XXX as select * from XXX; 這種創(chuàng)建表的語(yǔ)法，但是MySQL5.7.x版本里面gtid是開(kāi)啟的，會(huì)報(bào)錯(cuò)

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

官方說(shuō)明：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-restrictions.html

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.

解決辦法關(guān)閉GTID模式：

my.cnf里面修改參數(shù)為：

gtid_mode = OFF
enforce_gtid_consistency = OFF

重啟MySQL，再次創(chuàng)建成功：

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)

到此這篇關(guān)于mysql錯(cuò)誤處理之ERROR 1786 (HY000)的文章就介紹到這了,更多相關(guān)mysql錯(cuò)誤處理之ERROR 1786 (HY000)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！