The replica receives a statement that is unsafe while temporary tables remain open; MySQL issues warning 3022 to prevent data divergence.
ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO warns that open temporary tables exist on the MySQL replica, making incoming statements unsafe. Let all temp tables close or drop them until variable slave_open_temp_tables equals 0, then retry the operation to clear the warning.
ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO
MySQL raises warning code 3022 when a replication slave still holds open temporary tables and receives a statement that could modify data inconsistently. The server blocks the operation to keep master and replica in sync.
The condition name ER_WARN_OPEN_TEMP_TABLES_MUST_BE_ZERO appears from MySQL 5.7.4 onward and signals that the system variable slave_open_temp_tables is greater than zero.
Executing DML or DDL while temp tables persist can create divergent data between master and replica, leading to replication errors later. Clearing the warning ensures deterministic replication.
The most frequent cause is long-running sessions that create temporary tables and never drop them, leaving replica state dirty.
Another cause is application code that creates temp tables under autocommit false and disconnects unexpectedly, so cleanup never occurs.
High connection churn combined with SET GLOBAL sql_log_bin = 0 statements may also trigger the issue because temp tables stay open across transactions.
First, verify the count of open temp tables on the replica. If the number is non-zero, drop those tables manually or restart the replica.
Ensure the application gracefully drops all temporary tables before committing or closing connections. Then rerun the failed statement.
Scenario: Long analytics query created temp tables and connection died. Solution: Identify the session with SHOW PROCESSLIST and KILL it, then run DROP TEMPORARY TABLE if necessary.
Scenario: Automated ETL job leaves temp tables. Solution: Refactor ETL scripts to add DROP TEMPORARY TABLE IF EXISTS at end.
Always drop temp tables explicitly in code. Wrap creation and drop in TRY...FINALLY blocks where possible.
Monitor slave_open_temp_tables and alert when value exceeds threshold, using tools such as Prometheus or MySQL Performance Schema.
Code 3020 ER_SLAVE_CONVERSION_FAILED may occur if data divergence already happened; run pt-table-checksum to validate replicas.
Code 1201 ER_FUNCTION_NOT_DEFINED warns when stored functions differ between master and replica; deploy consistent schema migrations.
Open temp tables created by long-running reporting sessions that never issue DROP TEMPORARY TABLE.
Unexpected client disconnects while temp tables are active, especially over unstable networks.
Stored procedures that create temp tables and exit early on error without cleanup.
Large ETL pipelines that hold temp tables across multiple replicated statements.
Fatal slave errors stop replication entirely. Often follow ignored warnings like 3022.
Occurs when data types cannot convert during replication. May arise after inconsistent temp table handling.
Value truncation can cascade into replication lag if temp tables store improper data lengths.
No, replication continues but the operation may be skipped or flagged, risking data mismatch.
Ignoring is risky. Temporary tables may hide divergence that surfaces later.
Restarting the replica clears temp tables but dropping them manually is faster and avoids downtime.
Galaxy highlights replication warnings in query results and lets teams store verified cleanup scripts in shared Collections.
.avif)
.avif){kind=logo}
