Bypassing GTID Restrictions on CREATE TABLE … SELECT and Temporary Tables in MySQL 5.6/5.7 and Their Removal in MySQL 8.0

MySQL 5.6 and 5.7 impose two hard GTID‑consistency restrictions: CREATE TABLE ... SELECT statements and temporary‑table DDL statements cannot be executed inside transactions, procedures, functions, or triggers when enforce_gtid_consistency is ON.

CREATE TABLE ... SELECT statements. When binlog_format is STATEMENT, the statement is logged as a single GTID transaction, but with ROW format it would generate two GTIDs, causing inconsistency; therefore it is disallowed. Temporary tables. CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE are prohibited inside transactional contexts when GTIDs are enabled.

To work around the first restriction, split the operation into two separate statements, for example using CREATE TABLE ... LIKE to clone the structure and then INSERT ... SELECT to copy data:

mysql:ytt:5.7.34-log> create table trans1(id int primary key, log_date date);
Query OK, 0 rows affected (0.03 sec)

mysql:ytt:5.7.34-log> insert trans1 values (1,'2022-01-02');
Query OK, 1 row affected (0.00 sec)

mysql:ytt:5.7.34-log> create table trans2 as select * from trans1;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

After splitting:

mysql:ytt:5.7.34-log> create table trans2 like trans1;
Query OK, 0 rows affected (0.02 sec)

mysql:ytt:5.7.34-log> insert trans2 select * from trans1;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

The resulting binlog shows two separate GTID events, preserving consistency.

For temporary tables, execute the DDL outside any transaction, then use the temporary table within the transaction:

mysql:ytt:5.7.34-log> create temporary table tmp(id int,log_date date);
Query OK, 0 rows affected (0.00 sec)

mysql:ytt:5.7.34-log> begin;
Query OK, 0 rows affected (0.01 sec)

mysql:ytt:5.7.34-log> insert tmp values (100,'2022-10-21');
Query OK, 1 row affected (0.01 sec)

mysql:ytt:5.7.34-log> insert trans1 select * from tmp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql:ytt:5.7.34-log> commit;
Query OK, 0 rows affected (0.00 sec)

MySQL 8.0 introduces native DDL atomicity, which removes both GTID restrictions. In 8.0, CREATE TABLE ... LIKE generates a single GTID, and temporary‑table DDL can be safely used inside transactions:

mysql:ytt:8.0.29> create table trans2 as select * from trans1;
Query OK, 1 row affected (0.08 sec)

mysql:ytt:8.0.29> begin;
Query OK, 0 rows affected (0.01 sec)

mysql:ytt:8.0.29> create temporary table tmp(a int,b date);
Query OK, 0 rows affected (0.00 sec)

mysql:ytt:8.0.29> insert tmp values (10,'2022-12-31');
Query OK, 1 row affected (0.00 sec)

mysql:ytt:8.0.29> insert trans1 select * from tmp;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql:ytt:8.0.29> commit;
Query OK, 0 rows affected (0.01 sec)

Thus, upgrading to MySQL 8.0 eliminates the need for the work‑arounds described above.