mysql错误处理之ERROR 1786 (HY000)

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.

官方说明：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.

解决办法关闭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)