📋

Description

This error occurs when attempting to create or drop a temporary table within a transactional context, such as an explicit transaction block, a stored function, or a trigger, while MySQL's Global Transaction Identifiers (GTID) are enabled. MySQL enforces this restriction to maintain GTID consistency across replicas, as temporary table operations are not replicated via GTID.

💬

Error Message

Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context. These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.

🔍

Known Causes

4 known causes

⚠️

Temporary Table in Explicit Transaction

You attempted to execute `CREATE TEMPORARY TABLE` or `DROP TEMPORARY TABLE` within an active transaction block (e.g., after `START TRANSACTION` and before `COMMIT` or `ROLLBACK`).

⚠️

Temporary Table in Stored Function

The statement to create or drop a temporary table was part of a stored function, which MySQL implicitly treats as a multi-statement transaction, violating GTID consistency rules.

⚠️

Temporary Table in Database Trigger

The `CREATE TEMPORARY TABLE` or `DROP TEMPORARY TABLE` statement was invoked from within a database trigger, which also operates in a transactional context not compatible with GTID consistency for these operations.

⚠️

Active GTID Consistency Mode

MySQL's Global Transaction Identifiers (GTID) are enabled on the server, requiring strict consistency rules for transactional operations, including temporary table management, to ensure replication integrity.

🛠️

Solutions

3 solutions available

1. Execute Temporary Table Operations Outside Transactions easy

Ensure that CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not part of a BEGIN...COMMIT block.

1

Identify the transaction that contains the temporary table operations. This typically involves looking for `BEGIN;` or `START TRANSACTION;` and `COMMIT;` or `ROLLBACK;` statements surrounding your `CREATE TEMPORARY TABLE` or `DROP TEMPORARY TABLE` statements.

2

Separate the temporary table creation and dropping into individual statements outside of any explicit transaction. If the temporary table is needed for a series of operations, create it before the transaction begins and drop it after the transaction commits.

CREATE TEMPORARY TABLE my_temp_table (id INT);

-- Start of transactional operations
START TRANSACTION;
-- ... your other SQL statements ...
COMMIT;

-- Drop temporary table after transaction is done
DROP TEMPORARY TABLE my_temp_table;

2. Refactor Stored Procedures and Functions medium

Rewrite stored procedures and functions to avoid using temporary tables within their execution context.

1

Examine the code of your stored procedures and functions. Look for any `CREATE TEMPORARY TABLE` or `DROP TEMPORARY TABLE` statements.

2

Consider alternative approaches. Instead of temporary tables, you might be able to use:
- Table variables (if your MySQL version supports them, though less common than in SQL Server).
- Common Table Expressions (CTEs) for complex queries.
- Intermediate result sets stored in regular tables (if data persistence is acceptable).
- Passing data as parameters or returning result sets directly.

3

If using CTEs, structure your query like this:

WITH my_cte AS (
  SELECT column1, column2 FROM some_table WHERE condition
)
SELECT * FROM my_cte WHERE another_condition;

4

If you absolutely must use temporary storage, consider creating and dropping the temporary table in the calling script or procedure that invokes the function/procedure, rather than within the function/procedure itself.

DELIMITER //
CREATE PROCEDURE my_procedure_using_temp_table() 
BEGIN 
  -- This is now considered unsafe with GTID
  -- CREATE TEMPORARY TABLE ... 
  -- DROP TEMPORARY TABLE ...
END //
DELIMITER ;

-- In your calling script:
CREATE TEMPORARY TABLE temp_data (id INT);
-- Populate temp_data
CALL my_procedure_using_temp_table();
DROP TEMPORARY TABLE temp_data;

3. Disable GTID for Specific Operations (Use with Caution) advanced

Temporarily disable GTID enforcement for specific commands that use temporary tables, if absolutely necessary and understood.

1

Understand the implications. Disabling GTID for a session or specific statements can lead to replication inconsistencies if not managed carefully. This is generally a last resort or for specific debugging scenarios.

2

To disable GTID for the current session, you can set the `gtid_next` variable. This is typically done before the statement that violates GTID consistency.

SET @@SESSION.gtid_next = 'AUTOMATIC';

-- Now execute your statement that uses temporary tables
CREATE TEMPORARY TABLE my_temp_table (id INT);
-- ... other statements ...
DROP TEMPORARY TABLE my_temp_table;

-- Reset gtid_next to its previous value or let it revert when the session ends
-- SET @@SESSION.gtid_next = @@SESSION.gtid_next; -- Or similar logic if needed

3

Alternatively, for certain scenarios, you might set `ENFORCE_GTID_CONSISTENCY=0` at the server level (requires server restart and is highly discouraged for production). The `gtid_next` session variable approach is safer for temporary workarounds.

🔗

Related Errors

5 related errors

MySQL Error 1787: GTID Unsafe Temporary Table Usage