Resolving MySQL GTID Consistency Errors When Using CREATE TABLE ... SELECT

Background: A financial company migrated its system from MySQL 5.6 to 5.7.30, enabling GTID after the migration. When using CREATE TABLE ... SELECT ... to import data, the following error occurs:

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

Problem principle: The issue arises because MySQL 5.7 with GTID enabled sets the enforce_gtid_consistency parameter, which requires GTID consistency for all statements. In GTID mode, DDL and DML generate separate GTIDs, but CREATE TABLE ... SELECT ... generates only one GTID, merging DDL and DML into a single transaction, which can lead to data inconsistency if the statement fails. The enforce_gtid_consistency parameter enforces atomicity of GTID transactions.

The official documentation also describes the restrictions on CREATE TABLE ... SELECT ... under GTID consistency.

Solution: For safety, it is not recommended to disable the parameter. Instead, split the operation into two separate SQL statements on MySQL 5.7:

# First create the table
CREATE TABLE ... LIKE ...

# Then insert the data
INSERT INTO ... SELECT ...

Although the single‑statement approach is simpler, the two‑step method ensures safer, atomic execution.

MySQL 8.0: Good news—starting from MySQL 8.0.21, the storage engine supports atomic DDL, allowing the use of CREATE TABLE ... SELECT ... without violating GTID consistency.

Keywords: #MySQL #GTID #Transaction

Reading recommendations:

Technical Share | MySQL Data Import Solutions

Technical Share | MongoDB and Redis Master‑Slave Sync Issues

Technical Share | When Do MySQL Permission Changes Take Effect?

Event promotion: OceanBase community visited vivo in Shenzhen on October 21, presenting a technical exchange on domestic database applications. Liu Shihong, a solution architect, delivered a lightning talk titled "ActionDB helps enterprises upgrade domestic databases".

About SQLE: SQLE is a SQL audit tool for database users and administrators, supporting multi‑scenario audits, standardized release processes, native MySQL auditing, and extensible to other database types, with over 700 rules.

SQLE acquisition links:

🔗 Github: https://github.com/actiontech/sqle

📚 Documentation: https://actiontech.github.io/sqle-docs/

💻 Official site: https://opensource.actionsky.com/sqle/

👥 WeChat group: add admin WeChat ActionOpenSource