MySQL GTID consistency violation

Question

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 1

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; 

Answer 2

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.