How to fix ERROR 1566: Not allowed to modify binlog in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 206 views

MySQL ERROR 1566 occurs when you attempt to modify binary log format settings while temporary tables are open in the session. This replication safety mechanism prevents data inconsistency between primary and replica servers. Fixing requires closing all temporary tables before changing binlog_format settings.

Quick fix

Fixes 1566

-- Check all temporary tables in the current session:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'TEMPORARY'
AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- Check if replication applier threads have temporary tables:
SHOW PROCESSLIST;
-- Look for any "applier" or "SQL thread" entries, then check what they are doing
  1. Verify the temporary tables causing the error
  2. Drop all temporary tables before changing binlog_format
  3. Change the binlog_format after clearing temporary tables
  4. Verify binlog_format change and test replication
mysql — error
-- Check all temporary tables in the current session:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'TEMPORARY'
AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');
-- Check if replication applier threads have temporary tables:
SHOW PROCESSLIST;
-- Look for any "applier" or "SQL thread" entries, then check what they are doing
ERROR 1566: Not allowed to modify binlog

What this error means

MySQL ERROR 1566 (ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR) is a replication safety error that prevents changing the binary log format when temporary tables exist in the current session. The binary log format (STATEMENT, ROW, or MIXED) determines how MySQL records changes for replication. Temporary tables behave differently depending on the format: in ROW or MIXED mode, temporary table operations are not replicated; in STATEMENT mode, they are. Switching formats with open temporary tables could corrupt replication state. This error protects against situations where a temporary table created in one replication format mode could be visible in a different format after the mode changes, causing replicas to receive inconsistent instructions. MySQL enforces this restriction both at the session level (SET @@SESSION.binlog_format) and global level (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format).

Typical symptoms

  • SET @@SESSION.binlog_format or SET @@GLOBAL.binlog_format commands fail with ERROR 1566
  • Application or script fails when attempting to change binlog_format mid-session
  • Error occurs even when binlog_format setting is the same (you are not changing it)
  • Temporary tables created by joins, subqueries, or explicit CREATE TEMPORARY TABLE prevent format changes
  • Error disappears after disconnecting and reconnecting in a new session
  • Replication applier threads cannot switch replication format due to temporary tables

Common causes

  1. Attempting to change @@SESSION.binlog_format while a temporary table is open in the current session
  2. Attempting to change @@GLOBAL.binlog_format while any replication channel applier thread has open temporary tables
  3. Attempting to change from ROW or MIXED back to STATEMENT format with active temporary tables (most restrictive direction)
  4. Implicit temporary tables created during complex joins or subquery execution blocking format changes
  5. Replication applier thread (SQL thread) with residual temporary tables from previous statements

How to fix "ERROR 1566: Not allowed to modify binlog"

1Verify the temporary tables causing the error

First, identify which temporary tables are blocking the binlog_format change.

sql
-- Check all temporary tables in the current session:
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'TEMPORARY'
AND TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

-- Check if replication applier threads have temporary tables:
SHOW PROCESSLIST;
-- Look for any "applier" or "SQL thread" entries, then check what they are doing

Temporary tables are session-specific and disappear when the connection closes, but they persist for the duration of the session.

2Drop all temporary tables before changing binlog_format

Close or drop all temporary tables in your current session.

sql
-- If you know the temporary table names:
DROP TEMPORARY TABLE IF EXISTS temp_table_1, temp_table_2;

-- Or, disconnect and reconnect in a new session (cleanest approach):
-- Simply close the current MySQL connection and create a new one

-- For application code (Node.js/mysql example):
// Close the current connection
await connection.end();
// Create a new connection
const newConnection = await mysql.createConnection(config);

Note: Temporary tables exist only for the current session. If you disconnect and reconnect, all temporary tables are automatically dropped.

3Change the binlog_format after clearing temporary tables

Once all temporary tables are closed, you can safely change the binlog_format.

sql
-- Change at SESSION level (for current connection only):
SET @@SESSION.binlog_format = 'ROW';
-- or
SET @@SESSION.binlog_format = 'STATEMENT';
-- or
SET @@SESSION.binlog_format = 'MIXED';

-- Change at GLOBAL level (requires SUPER or SYSTEM_VARIABLES_ADMIN privilege):
SET @@GLOBAL.binlog_format = 'ROW';

-- Make the change persistent across restarts (MySQL 5.7.11+, requires SUPER or SYSTEM_VARIABLES_ADMIN):
SET @@PERSIST.binlog_format = 'ROW';

-- Verify the change:
SHOW VARIABLES LIKE 'binlog_format';

The change takes effect immediately for new queries in the session.

4Verify binlog_format change and test replication

Confirm the format change and ensure replication continues working correctly.

sql
-- Check the current binlog_format:
SHOW VARIABLES LIKE 'binlog_format';

-- Check binlog status on the primary:
SHOW MASTER STATUS;

-- On the replica, verify replication is running:
SHOW REPLICA STATUS\G
-- Or (MySQL 5.7):
SHOW SLAVE STATUS\G

-- Verify no replication lag (Seconds_Behind_Master should be 0 or small):
SHOW REPLICA STATUS\G | grep "Seconds_Behind_Master";

If replication has lag, wait for it to catch up before making more changes.

5Use SET @@PERSIST_ONLY for global changes without replication conflicts

For persistent global changes without runtime impact (requires restart), use PERSIST_ONLY.

sql
-- This takes effect only after server restart, avoiding conflicts:
SET @@PERSIST_ONLY.binlog_format = 'ROW';

-- Verify it was set for next restart (check config):
SELECT * FROM performance_schema.variables_info WHERE VARIABLE_NAME = 'binlog_format';

-- The current runtime value remains unchanged until restart:
SHOW VARIABLES LIKE 'binlog_format';

PERSIST_ONLY is useful for scheduled maintenance when you want to avoid immediate replication impact.

6For replication applier thread issues, restart the replication applier

If the error occurs on a replica when the applier thread has temporary tables, restart replication.

sql
-- Stop the replica (MySQL 8.0.22+):
STOP REPLICA;

-- Or for older MySQL versions:
STOP SLAVE;

-- Wait a moment, then restart:
START REPLICA;

-- Or (older versions):
START SLAVE;

-- Verify replication thread status:
SHOW REPLICA STATUS\G

-- Check for errors:
SHOW REPLICA STATUS\G | grep -E "Replica_IO_Running|Replica_SQL_Running|Last_Error";

This clears any residual temporary tables the applier thread was holding.

Advanced notes

In replication scenarios, ERROR 1566 can be particularly tricky. The primary server may allow format changes, but the replica applier thread cannot if it has open temporary tables. This happens when the replica is executing statements that create temporary tables, and you attempt to change @@GLOBAL.binlog_format on the primary.

MySQL 5.7 introduced stricter enforcement of this rule. In MySQL 8.0+, the error is more likely to appear because temporary tables from complex joins and subqueries are managed more aggressively. When changing binlog_format in a multi-tier replication setup (primary → replica → cascading replica), ensure all replication channels are idle before changing the format.

For applications using connection pooling, beware: a pooled connection may retain temporary tables from previous queries. Always call DISCARD SESSION STATE or explicitly drop temporary tables before reusing a connection for format changes.

The restriction is directional: you can usually change from STATEMENT to ROW/MIXED with temporary tables present, but changing from ROW or MIXED back to STATEMENT is strictly forbidden with open temporary tables. This is because STATEMENT mode uses fewer resources for temporary tables.

If you encounter this error during a migration or upgrade, the safest approach is to accept the brief downtime of closing all client connections and restarting the MySQL server with the new binlog_format in the configuration file. This avoids runtime conflicts entirely.

Frequently asked questions

What does "1566" mean?
MySQL ERROR 1566 (ER_TEMP_TABLE_PREVENTS_SWITCH_OUT_OF_RBR) is a replication safety error that prevents changing the binary log format when temporary tables exist in the current session. The binary log format (STATEMENT, ROW, or MIXED) determines how MySQL records changes for replication. Temporary tables behave differently depending on the format: in ROW or MIXED mode, temporary table operations are not replicated; in STATEMENT mode, they are.
How do I fix "1566"?
MySQL ERROR 1566 occurs when you attempt to modify binary log format settings while temporary tables are open in the session. This replication safety mechanism prevents data inconsistency between primary and replica servers. Fixing requires closing all temporary tables before changing binlog_format settings.
What causes "1566"?
Attempting to change @@SESSION.binlog_format while a temporary table is open in the current session Attempting to change @@GLOBAL.binlog_format while any replication channel applier thread has open temporary tables Attempting to change from ROW or MIXED back to STATEMENT format with active temporary tables (most restrictive direction)
Is the "1566" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading