MySQL Error 1787: ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION - Fix and Prevention Guide

What does MySQL Error 1787 mean?

The server throws error code 1787 with condition name ER_GTID_UNSAFE_CREATE_DROP_TEMPORARY_TABLE_IN_TRANSACTION when a CREATE TEMPORARY TABLE or DROP TEMPORARY TABLE command runs inside a transactional context while GTID_MODE is ON. GTID requires every transaction to be fully repeatable during replication, and temporary tables break that guarantee.

The error halts the current statement, rolls back the transaction, and returns SQLSTATE HY000. It protects GTID consistency so replicas can apply transactions deterministically.

When does the error typically occur?

It shows up during explicit transactions started with START TRANSACTION, within stored functions or triggers (each treated as an implicit multi-statement transaction), and sometimes in client libraries that implicitly wrap statements in a transaction.

Systems upgrading to GTID replication often first encounter the error because older code created temp tables inside transactions without issue in non-GTID modes.

Why is fixing it important?

Unresolved, the error blocks data-change workflows, breaks application logic, and prevents migrations to GTID-based replication topologies. Clearing the violation restores transactional flow and ensures replica safety.

What Causes This Error?

Executing CREATE TEMPORARY TABLE inside BEGIN ... COMMIT while GTID_MODE = ON triggers the violation.

DRO P TEMPORARY TABLE inside any transaction context has the same effect.

Calling a stored function or trigger that itself contains temp table statements violates GTID rules because those objects execute as a single implicit transaction.

How to Fix MySQL Error 1787

Move all temporary table DDL outside any explicit or implicit transaction. Commit or roll back first, then create or drop the temp table as an independent auto-commit statement.

If code refactor is impossible, disable GTID temporarily with SESSION sql_log_bin = 0; or SET SESSION GTID_NEXT='AUTOMATIC'; run the temp table DDL, then re-enable normal GTID handling.

For one-off maintenance, set global gtid_mode = OFF_PERMISSIVE, execute the script, then restore gtid_mode = ON. Always test on a replica before production.

Common Scenarios and Solutions

Application transaction using temp staging table - split the logic: first create the temp table, then open the transaction for data inserts and updates.

Stored procedure builds a temp table and later drops it - add a COMMIT between temp table DDL and the rest of the logic, or redesign to use derived tables.

Trigger creates temp table for auditing - move auditing into an AFTER trigger that writes to a regular table or use session variables instead.

Best Practices to Avoid This Error

Keep all CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements in auto-commit mode.

Audit stored procedures, functions, and triggers before enabling GTID replication to ensure no temp table DDL exists inside them.

Use derived tables, common table expressions, or staging tables truncated after use instead of temporary tables in long transactions.

Galaxy users can rely on instant linting; the editor warns when temp table DDL appears inside BEGIN ... END under GTID_MODE = ON, saving debugging time.

Related Errors and Solutions

Error 1785 ER_GTID_UNSAFE_AUTOCOMMIT_USAGE appears when an autocommit statement mixes with unsafe constructs in a transaction. Solution: disable autocommit or refactor.

Error 1786 ER_GTID_UNSAFE_CREATE_SELECT triggers on CREATE TABLE ... SELECT inside a transaction. Fix by splitting the statement from the transaction.

Error 1830 ER_GTID_MODE requires GTID replication to be fully enabled on all servers. Address by setting gtid_mode and enforcing synced status.