<p>The error occurs when an update mixes transactional and non-transactional tables while GTID consistency is enforced.</p>
<p>MySQL Error 1785: ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE appears when a statement updates MyISAM or other non-transactional tables in the same transaction as InnoDB tables while GTID consistency is ON. Use autocommit, isolate the update into its own single-statement transaction, or convert the non-transactional tables to InnoDB to resolve the problem.</p>
Statement violates GTID consistency: Updates to
MySQL raises error 1785 when GTID_MODE is ON and a statement violates Global Transaction ID (GTID) consistency rules by touching non-transactional tables in a multi-statement transaction.
The server blocks the query to guarantee that every transaction can be replicated deterministically across replicas.
The error shows up in three main situations: mixing MyISAM and InnoDB updates in one transaction, wrapping a non-transactional DML statement in an explicit multi-statement transaction, or combining DDL and DML on non-transactional tables.
Autocommit queries that only target non-transactional tables do not trigger the error.
Ignoring the error stalls application workflows and breaks replication flow. Fixing it maintains data integrity, keeps replicas in sync, and prevents application downtime.
Production environments with GTID-based replication demand strict consistency, so the fix should be applied before retrying the workload.
Galaxys SQL editor highlights mixed engine usage, offers AI suggestions to refactor statements, and lets teams share the corrected queries. Its version control tracks engine conversions, giving developers confidence before deploying changes.
Updating an InnoDB table and a MyISAM table in the same multi-statement transaction violates GTID rules.
Wrapping non-transactional updates inside BEGIN and COMMIT creates a multi-statement transaction that GTID cannot safely replicate.
Turning off autocommit forces every statement into a transaction, so even single updates to MyISAM tables will fail.
Triggers that modify a MyISAM table after changing an InnoDB table cause implicit mixed-engine transactions.
Occurs when creating or dropping temporary tables in multi-statement transactions under GTID mode.
Raised when CREATE TABLE ... SELECT mixes DDL and DML with GTID enabled.
Triggered by temporary table operations inside explicit transactions.
Disabling GTID removes the restriction but sacrifices replication consistency. It is better to fix the statement.
Yes, if your statement touches only non-transactional tables. Mixed-engine updates still require separation or conversion.
Run SHOW TABLE STATUS WHERE Engine = 'MyISAM' or query information_schema.TABLES to list them.
ALTER TABLE ... ENGINE=InnoDB is blocking in older versions. Use pt-online-schema-change or MySQL 8.0 instant ALTER to minimize downtime.
.avif)
.avif){kind=logo}
