ERROR 1786 (HY000)

【错误代码】

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

【原因分析】

是由于启用enforce_gtid_consistency功能导致

1 mysql> show variables like 'enforce_gtid%';
2 +--------------------------+-------+
3 | Variable_name            | Value |
4 +--------------------------+-------+
5 | enforce_gtid_consistency | ON    |
6 +--------------------------+-------+
7 1 row in set (0.01 sec)

而该功能会限制数据库只允许执行被日志记录的语句，该参数值设置说明：

官方原文解释

When enabled, the server enforces GTID consistency by allowing execution of only statements that can be safely logged using a GTID. You must set this option to ON before enabling GTID based replication.

 

Only statements that can be logged using GTID safe statements can be logged when enforce-gtid-consistency is set to ON, so the operations listed here cannot be used with this option:

  • CREATE TABLE ... SELECT statements

  • CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE statements inside transactions

  • Transactions or statements that update both transactional and nontransactional tables. There is an exception that nontransactional DML is allowed in the same transaction or in the same statement as transactional DML, if all nontransactional tables are temporary.

 【解决方法】

通过set global enforce_gtid_consistency=off来修改

posted @ 2017-09-18 10:12  tonnytangy  阅读(506)  评论(0)  编辑  收藏  举报