How to Fix MySQL Error 1785 (ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE)
You get mysql error 1785 when a single transaction updates both transactional and non-transactional tables. You can fix it by converting all tables to a transactional engine or splitting the queries. MySQL uses global transaction identifiers to track data changes across primary and replica servers. Each change gets one unique ID in the binary log. When a query mixes table types, the database cannot keep the replica log safe. The server blocks the write and returns symbol ER_GTID_UNSAFE_NON_TRAN
Your coding agents run with your access.
Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.
You get mysql error 1785 when a single transaction updates both transactional and non-transactional tables. You can fix it by converting all tables to a transactional engine or splitting the queries.
MySQL uses global transaction identifiers to track data changes across primary and replica servers. Each change gets one unique ID in the binary log. When a query mixes table types, the database cannot keep the replica log safe. The server blocks the write and returns symbol ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE with SQL state HY000.
Why mysql error 1785 happens in replication setups
Transactional tables can roll back changes if a query fails halfway through execution. Non-transactional tables write data to disk right away and cannot roll back those changes. When you mix both in one transaction, one part can fail while the other stays. This splits one logical transaction into two different events in the binary log.
With GTID consistency turned on, the server enforces strict rules to stop replication drift. The official error message states that updates to non-transactional tables violate GTID consistency. The database only allows these updates in single statements with autocommit turned on. It also allows them when the entire transaction touches only non-transactional tables.
Even a simple read on a transactional table followed by a write can trigger this. Database triggers are another common hidden cause for this failure in production apps. If a write to a transactional table fires a trigger that logs to an old table, it fails.
How to find the unsafe queries and tables
First, check your app error logs to find the exact statement that failed. Next, inspect every table named in that query to see which storage engine it uses. Do not forget to check views and triggers linked to those tables.
A view might join a modern transactional table with an older legacy lookup table. A trigger might write audit rows into a separate non-transactional log table. You can query the system schema metadata to list all tables that do not use a transactional engine. This check gives your team a clear list of tables to update.
Non-Human Identity Management + MySQL Access Governance: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
You should also check temporary tables created inside stored procedures during the session. Many legacy scripts create temporary tables using old storage engines by mistake. When those temporary tables join with standard tables in a transaction, the error fires.
How to fix the error safely in production
The best fix is to change the storage engine of the old tables to a transactional one. Plan this change during low traffic hours because altering a table can lock writes. Test the engine change on a staging copy first to check app performance. Once all tables use the same transactional engine, the server can group changes under one ID.
If you cannot alter the table right away, change how your app sends the queries. Move the write to the non-transactional table out of the main transaction block. Run that write on its own session with autocommit enabled so it gets its own ID.
Never turn off GTID enforcement on a live cluster just to bypass this error. Turning off safety checks puts your replicas at risk of silent data drift. Keep your database permissions tight while your team fixes the schema or app code. Only grant alter privileges to the specific role running the migration.
Add wire guardrails and masking beside MySQL
Database grants and TLS setup decide who can reach MySQL. Even so, an open session can run broad updates or read raw personal data from allowed tables. In practice, hoop.dev sits inline in the wire protocol beside the database as a sidecar for your MySQL. On every production query an agent or engineer sends, hoop.dev decides to pass, mask, block, or escalate it for human review.
With hoop.dev MySQL support, guardrails refuse bad queries before they run or hold risky calls for approval, while data masking rewrites matched sensitive values in result rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for GDPR, HIPAA, and PCI DSS programs. Read the docs and source on GitHub.
Frequently asked questions
What does ER_GTID_UNSAFE_NON_TRANSACTIONAL_TABLE mean?
It means a transaction tried to mix transactional and non-transactional table updates while GTID mode is active. MySQL blocks the query so primary and replica logs stay in sync.
Can a database trigger cause mysql error 1785?
Yes, a trigger runs inside the same transaction as the main query. If the main query uses a transactional table and the trigger writes to a non-transactional table, the query fails.
Is it safe to disable GTID consistency checks to stop the error?
No, disabling consistency checks can break replication when a mixed transaction fails halfway through. Always convert the table engine or split the transaction into separate autocommitted queries instead.
Related guides
In this post