MySQL错误ERROR 1786 (HY000)解决

最新推荐文章于 2024-03-14 05:39:22 发布

原创最新推荐文章于 2024-03-14 05:39:22 发布 · 2.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

1 篇文章

订阅专栏

本文探讨了MySQL5.7.x版本中GTID模式下使用CREATETABLE...SELECT语句创建表的问题，该操作可能导致GTID一致性错误。文章详细解释了这一限制的原因，并提供了关闭GTID模式的具体步骤，以确保此类语句能够成功执行。

务上需要支持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)
----------当你发现自己的才华撑不起野心时，就请安静下来学习吧---------