Fixing MySQL ERROR 1785: Statement Violates GTID Consistency

The ProblemYou hit a wall while trying to clone a table or save query results using the CREATE TABLE ... SELECT (CTAS) shortcut. This command is a favorite for developers who need a quick backup, but it fails in modern high-availability environments. If your MySQL instance uses Global Transaction Identifiers (GTID)—standard for AWS RDS, Aurora, or any robust master-slave setup—you will see this error:

ERROR 1785 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

MySQL is protecting you. It enforces strict rules to ensure every change can be tracked across your entire cluster without data drifting between servers.

Why This HappensTo see if your server is in this strict mode, run this check:

SHOW VARIABLES LIKE 'enforce_gtid_consistency';

If the result is ON, MySQL becomes picky about how it logs transactions. The CREATE TABLE ... SELECT statement is problematic because it tries to do two different things at once: it defines a new schema (DDL) and populates it with rows (DML). In versions before MySQL 8.0.21, the engine cannot log these two distinct actions as a single, atomic GTID event. If the master server crashes halfway through, or a replica lags, the database can't guarantee that the new table will look exactly the same on every node in the cluster. To prevent this inconsistency, MySQL simply blocks the command.

The Solution: Refactor into Two StepsThe fix is straightforward. You need to stop trying to do everything in one line. By splitting the operation, you give the GTID engine clear, loggable steps that won't break replication.

1. Define the Table StructureFirst, create an empty shell. If you want an exact copy of an existing table (including indexes), use the LIKE clause. If you only need specific columns, define them manually.

-- Option A: Clone the exact structure (recommended)
CREATE TABLE orders_backup LIKE orders;

-- Option B: Create a custom structure for specific data
CREATE TABLE monthly_summary (
    id INT PRIMARY KEY,
    total_sales DECIMAL(10,2),
    report_date DATE
);

2. Migrate the DataNow that the table exists, move the data using a standard INSERT INTO ... SELECT statement. This is a pure data operation that GTID handles without any complaints.

INSERT INTO monthly_summary (id, total_sales, report_date)
SELECT id, amount, '2023-12-31' 
FROM orders 
WHERE status = 'completed' AND created_at >= '2023-12-01';

For massive datasets—say, over 1 million rows—consider adding a LIMIT or processing the data in batches to avoid locking the source table for too long.

Verifying the ResultsAfter running your split commands, take 30 seconds to ensure everything is synced:

- **Confirm Row Counts:** Check if the numbers match your expectations.
    ```

SELECT COUNT(*) FROM monthly_summary;

    
    - **Check Replication Health:** If you are working on a production master, ensure the replicas are still happy.
        ```
SHOW REPLICA STATUS\G;

    Look for `Replica_IO_Running: Yes` and `Replica_SQL_Running: Yes`. If these say `No`, you may have a larger replication issue to solve.

Best Practices

- **Ditch CTAS in Scripts:** Avoid `CREATE TABLE ... SELECT` in any automated migration scripts or application code. It is a ticking time bomb that will fail the moment you migrate to a GTID-enabled environment.
- **Temporary Tables:** Be careful with `CREATE TEMPORARY TABLE`. Under GTID consistency, you generally cannot create temporary tables inside a transaction (between `BEGIN` and `COMMIT`).
- **Upgrade if Possible:** MySQL 8.0.21 introduced "Atomic DDL," which finally allows CTAS under GTID for InnoDB tables. If you are stuck on 5.7 or an early 8.0 release, upgrading can remove this headache entirely.