MySQL GTID integrity violation

Question

I am trying to create a table from a select statement, and this gives me a GTID integrity violation. [HY000][1786] Statement violates GTID consistency: CREATE TABLE ... SELECT.

create TABLE tags_mentions as
    select t.*, st.ts, m.user_id_from, m.user_id_to from Tags as t join Mentions as m
        on t.status_id = m.status_id AND m.user_id_from != m.user_id_to
        left join Statuses as st on t.status_id = st.status_id;

What is GTID consistency and how can I fix an SQL statement to avoid breaking?

Answer 1

CREATE TABLE... SELECTNot safe for agent-based replication. When using row-based replication, this statement is actually logged as two separate events — one to create the table, and the other to insert rows from the original table into the newly created new table.

, , , , , . CREATE TABLE... SELECT GTID.

Answer 2

https://dev.mysql.com/doc/refman/5.6/en/replication-options-gtids.html

--enforce-gtid-consistency - , , :

• CREATE TABLE... SELECT
• CREATE TEMPORARY TABLE
• , ,

, GTID. .

Answer 3

If you want to fix the error in another way, you can briefly create a table and insert it separately with:

CREATE TABLE new_table LIKE old_table; 
INSERT new_table SELECT * FROM old_table;

Answer 4

If you do not need to copy it to the slave, you can disable the log:

set sql_log_bin=0;
create table ... select ...