Troubleshooting MySQL Data Import Failures Post-Upgrade

Background​​

A financial company recently migrated their system from MySQL 5.6 to 5.7.30 and enabled GTID replication post-migration. When attempting to import data using the traditional method CREATE TABLE ... SELECT ..., they encountered an error:

Error Code: 1786 (HY000): MySQL Statement violates GTID consistency: CREATE TABLE ... SELECT.

‍

Problem Analysis

This error occurs due to MySQL 5.7’s enforcement of ​​GTID consistency​​ via the enforce_gtid_consistency parameter. In GTID mode, all transactions (including DDL and DML) must generate unique GTIDs to ensure atomicity across distributed systems. However, CREATE TABLE ... SELECT ... combines both DDL (table creation) and DML (data insertion) into a single transaction, violating GTID atomicity. If the statement fails midway, it risks data inconsistency between the primary and replica.

MySQL’s official documentation explicitly warns against this pattern in GTID-enabled environments:

"The CREATE TABLE ... SELECT statement is disallowed when enforce_gtid_consistency is enabled because it causes implicit commits and can lead to GTID inconsistencies."

‍

‍

Solution

To maintain safety and compliance with GTID constraints:

1. ​​Split the operation into two statements​​:

-- Step 1: Create the target table structure
CREATE TABLE new_table LIKE original_table;

-- Step 2: Insert data separately
INSERT INTO new_table SELECT * FROM original_table;

‍

This approach ensures DDL and DML are executed as separate transactions, preserving GTID atomicity.

2. MySQL 8.0+ Users​​:
Atomic DDL support (introduced in MySQL 8.0.21) allows CREATE TABLE ... SELECT ... to work with GTID consistency enabled. Verify compatibility if upgrading to MySQL 8.0 or later.

‍