<p>The session tries to change binary log format but still has open temporary tables.</p>
<p>MySQL Error 1559 ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR appears when you attempt to change the binlog_format from ROW to STATEMENT or MIXED while temporary tables remain open in the session. Close or drop every temp table or commit the transaction, then rerun SET SESSION binlog_format=STATEMENT to clear the error.</p>
Cannot switch out of the row-based binary log format when
The error message "Cannot switch out of the row-based binary log format when the session has open temporary tables" means MySQL blocks a request to change binlog_format because row based replication cannot be safely mixed with statement logging while temporary tables are active.
MySQL sets binlog_format at the session or global level. If temp tables exist, changing the format risks replication inconsistencies, so the server raises error 1559 and keeps the session in ROW mode.
The error surfaces immediately after running SET SESSION binlog_format=STATEMENT or MIXED while at least one temporary table created with CREATE TEMPORARY TABLE still exists in the current connection.
It also appears implicitly when a stored procedure or tool tries to toggle binlog_format internally and the connection owns unclosed temporary tables.
The blocked switch halts workflows that rely on statement logging, such as logical replication tools or point in time recovery strategies. Leaving your session stuck in ROW mode may inflate binary logs and slow replication.
Clearing the error quickly restores the intended binary logging strategy and protects replication consistency.
The most frequent cause is a CREATE TEMPORARY TABLE command that has not yet been followed by DROP TEMPORARY TABLE or an implicit connection close.
Transactions may keep temporary tables alive until COMMIT or ROLLBACK, preventing the switch.
Routines that create and reuse temporary tables internally often forget to drop them before changing binlog_format.
Backup or migration scripts that change binlog_format mid-session without temp-table hygiene trigger the error.
Raised when trying to update a view that is not updatable. Related because it also protects replication consistency.
Occurs when a statement considered unsafe for statement-based logging is executed with binlog_format=STATEMENT.
Requires SUPER privilege to create a routine while binary logging is enabled.
Yes. When the connection terminates, MySQL drops all temporary tables automatically, clearing the error condition.
A global switch is allowed only if no other session has open temporary tables. Otherwise MySQL blocks the command until all sessions are clean.
SET SQL_LOG_BIN=0 bypasses logging but affects replication integrity. Use only in isolated maintenance sessions.
Error 1559 appears in MySQL 5.1 and later, including 8.0, whenever the described condition occurs.
.avif)
.avif){kind=logo}
