MySQL GTID consistency violation

Jiho Noh picture Jiho Noh · Nov 21, 2016 · Viewed 32.4k times · Source

I am trying to create a table from a select statement, and it give me a GTID consistency 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 the SQL statement to avoid the violation?

Answer

David Thomas picture David Thomas · May 10, 2019

If you want to fix the error another way, you can concisely create the table and insert separately with:

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