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?

@Hogan Tags tables has 'tag' and 'status_id'. I can run the select statement alone and get the results. However creating table give the error. — Jiho Noh, Nov 21 '16 at 19:26
@GordonLinoff What do you mean by "replicated server"? I am using Datagrip (JetBrains application for database management) connecting to MySQL server on Google Cloud Platform. Hope this might be the information you are looking for. — Jiho Noh, Nov 21 '16 at 19:29

score 41 · Answer 1 · answered May 09 '19 at 23:06

41

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;

answered May 09 '19 at 23:06

David Thomas

4,027
3
28
22

score 18 · Accepted Answer · edited Aug 30 '18 at 09:16

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.

score 9 · Answer 3 · edited Aug 30 '18 at 09:17

9

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

Since only transactionally safe statements can be logged when --enforce-gtid-consistency is enabled, it follows that the operations listed here cannot be used with this option:

CREATE TABLE ... SELECT statements

CREATE TEMPORARY TABLE statements inside transactions

Transactions or statements that update both transactional and non-transactional tables

You seem to have enforce GTID set. So this statement is not allowed.

edited Aug 30 '18 at 09:17

Alessio Cantarella

5,077
3
27
34

answered Nov 21 '16 at 19:31

Hogan

69,564
10
76
117

This is great information. Do you have an idea why CREATE TABLE ... SELECT statement can be unsafe? Is it because the results of SELECT statement can be different at any time? Do I need root permission to disable this option? – Jiho Noh Nov 21 '16 at 19:35
Probably because it uses a temporary non transactional table before it builds the final table, but I don't know for sure. – Hogan Nov 21 '16 at 19:46
The workaround I use for this is to create a table with the same schema by copying the create statement for the original table and then do a ```INSERT ... SELECT``` statement: https://dev.mysql.com/doc/refman/8.0/en/insert-select.html – Stian Sep 05 '18 at 10:04
1

@Stian of course creating the table and inserting is a "work around". The question was how to NOT do that. – Hogan Sep 07 '18 at 04:08

score 1 · Answer 4 · answered Sep 05 '19 at 01:51

1

If you do not care to replicate it on the slave, you can turn off the binlog:

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

answered Sep 05 '19 at 01:51

Sasha Pachev

5,162
3
20
20

MySQL GTID consistency violation

4 Answers4