How to Fix MySQL Error 1787 (ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION)
You get mysql error 1787 when your app creates or drops a temporary table inside an active transaction while GTID consistency is on. To fix it, move temporary table creation and removal outside of multi-statement transactions or enable autocommit for those steps. Database clusters rely on Global Transaction Identifiers to keep replicas in sync with the primary server. Each transaction receives a unique identifier in the binary log. When a statement breaks this tracking model, the database engin
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 1787 when your app creates or drops a temporary table inside an active transaction while GTID consistency is on. To fix it, move temporary table creation and removal outside of multi-statement transactions or enable autocommit for those steps.
Database clusters rely on Global Transaction Identifiers to keep replicas in sync with the primary server. Each transaction receives a unique identifier in the binary log. When a statement breaks this tracking model, the database engine blocks the query right away. This stop protects your replica servers from data drift and replication halts.
What causes mysql error 1787 in database clusters
In the database engine, mysql error 1787 maps to the symbol ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION and uses SQLSTATE HY000. The server returns a clear message stating that the statement violates GTID consistency. It explains that creating and dropping temporary tables can only run outside of transactions.
The root cause sits in how the database handles rollbacks and binary logs. Temporary tables do not support standard transaction rollbacks like regular tables do. When a transaction creates a temporary table and then rolls back, the table stays in place. If the server writes this action into a single GTID transaction, the replica fails upon replay.
This failure often shows up when teams turn off autocommit in their database drivers. Object-relational mappers and reporting tools often wrap every query batch in an explicit transaction block. Stored functions and triggers also count as transactional contexts inside the engine. If a stored routine tries to build a temporary table, the engine rejects the call.
How to diagnose and fix temporary table failures safely
First, find the exact query or stored routine that triggers the failure in your error logs. Check if your app framework turns off autocommit by default for all database sessions. Many connection pools start a transaction as soon as a worker borrows a session. You must inspect your app code for explicit transaction start commands around temporary table queries.
The safest fix is to create and drop temporary tables before starting the main transaction. Make sure the session has autocommit turned on when you run the create statement. Once the temporary table exists, you can start your transaction and insert or read data. After you commit or roll back the work, drop the temporary table in autocommit mode.
Just-in-Time Access + MySQL Access Governance: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Another solid option is to switch to row-based binary logging across your database cluster. Row-based logging does not write temporary table events to the binary log at all. Check your server version rules to see if row format allows temporary tables inside transactions. You can also replace temporary tables with common table expressions or derived tables inside queries.
How to prevent transaction conflicts during live maintenance
Ad-hoc maintenance scripts often trigger this error when engineers run manual fixes on production data. An engineer might open a transaction to test a fix and create a temporary staging table. When the server blocks the statement, people feel tempted to turn off GTID enforcement. Never disable GTID consistency checks in production just to push a quick script through.
Instead, test all manual queries in a staging setup that matches your production replication rules. Review your database scripts to separate schema setup steps from data modification blocks. Good query review habits keep your replication stream healthy and protect sensitive fields like emails or phone numbers.
Inspect every MySQL query and response in flight
Static grants and network rules block bad logins. They cannot pause a risky query for review or mask emails in a result set. By design, hoop.dev sits inline in the MySQL protocol as a sidecar for your MySQL. For every plain query, prepared statement, or cursor an agent or human runs in production, hoop.dev decides to pass, mask, block, or hold it for review.
First, hoop.dev guardrails block banned actions before MySQL runs them, and an AI Analyzer you configure can score intent or escalate for approval. Next, hoop.dev data masking rewrites sensitive string columns in results in memory, and every verdict goes to the audit trail. Find the open-source binary on GitHub.
Frequently asked questions
Can I insert data into a temporary table inside a transaction?
Yes, you can insert, update, and delete rows in a temporary table inside a transaction. The restriction only applies to creating or dropping the temporary table itself.
Why does this error happen when I did not start a transaction?
Your database client or driver might have autocommit turned off for your session. When autocommit is off, every single query runs inside an implicit transaction block.
Does disabling GTID consistency checks fix this problem safely?
No, turning off GTID enforcement puts your replica servers at high risk of replication failure. Always fix the query structure instead of weakening your database safety rules.
Short role checks help teams work fast. Simple rules make each review clear. Good logs keep every team safe. Scoped keys stop bad changes early.
Teams test new role rules in staging first. Clear owner lists speed up each audit. Small role scopes cut down on risk. Regular log checks catch drift right away.
Related guides
In this post