Resolving GTID Consistency Errors When Updating InnoDB and MyISAM Tables in MySQL

Problem

A colleague encountered the error "Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single‑statement transactions, and never in the same statement as updates to transactional tables." The error occurs because a transaction tries to modify both an InnoDB (transactional) table and a MyISAM (non‑transactional) table, which MySQL disallows when enforce_gtid_consistency is ON.

Reproduction

Using MySQL 5.7.25 with GTID enabled, two tables were created: one InnoDB and one MyISAM. Updating both tables in the same transaction did not initially raise an error, but swapping the order of the updates (MyISAM first, then InnoDB) reproduced the client‑side error, confirming that MyISAM implicitly commits after BEGIN , breaking GTID consistency.

Root Cause

MyISAM does not support transactions; after BEGIN it performs an implicit commit. Subsequent updates to the MyISAM table are logged with a GTID but are not considered part of the same transaction, so when an InnoDB update follows, MySQL detects a mixed‑engine transaction and aborts.

Solutions

Modify application logic to separate the updates: execute the MyISAM statement outside the transaction and run the InnoDB statement in its own transaction.

Convert the MyISAM table to InnoDB (using ALTER TABLE … ENGINE=InnoDB or dump/reload with pt-online-schema-change for large tables).

Disable ENFORCE_GTID_CONSISTENCY , which allows mixed‑engine updates but makes transactions anonymous, breaks replication consistency, and is generally not recommended for production.

Verification of each approach showed that separating the statements is the safest, converting to InnoDB eliminates the problem entirely, and turning off GTID consistency leads to replication issues.

Note: MyISAM tables cannot be rolled back and lack many advanced features; it is advisable to migrate all tables to InnoDB as MySQL evolves.

Reference

MySQL documentation on GTID replication options and Percona Toolkit's pt-online-schema-change tool.