CODEWITHSUNDEEP
mysqldockerdocker-composegoogle-cloud-sql
Michal Tenenberg
Michal Tenenberg

Reputation: 518

create table as select working even with enforce-gtid-consistency

I have a sql script that contains the statement create tbl2 as select * from tbl;

This statement works on a docker mysql with version 5.7 (currently 5.7.19), even though enforce-gtid-consistency is turned on. Relevant lines from the docker compose yml are:

image: mysql:5.7
command: --gtid-mode=ON --enforce-gtid-consistency=true

This is even though the documentation clearly specifies:

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

The same statement fails with ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. on a google cloud mysql instance.

show variables like '%gtid%' returns the same result on the docker mysql and the google cloud instance (and enforce_gtid_consistency is on in both).

Upvotes: 4

Views: 2624

Answers (1)

Raj Ramani
Raj Ramani

Reputation: 26

Primary reason for failure you see is log-bin is NULL in container database though gtid_mode is on and enforce_gtid_consistency is on. So you must run container with log-bin set , setting this additionally also require to set server-id. Below is one example to run container where you should be able to reproduce error - " ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. "

docker run -d --name=my-mysql --env="MYSQL_ROOT_PASSWORD=rootpassword123" --publish 3306:3306 --volume=/u01/mysql:/var/lib/mysql mysql/mysql-server:5.7.20 --gtid_mode=ON --enforce_gtid_consistency=ON --log-bin=mysql-bin --master-info-repository=table --relay-log-info-repository=table --server-id=1

cheers raj

Upvotes: 1

Related Questions